Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Initial Programming Considerations : Data types : DataServer for MS SQL Server support for datetime data types : Performing data type conversions
 
Performing data type conversions
Making changes between ABL and MS SQL Server data types will affect how the data is stored. The following tables describe the effects of changing one data type to another.
The following table provides details on converting ABL DATETIME data types and MS SQL Server data types:
Table 13. Converting ABL datetime types to MS SQL Server data types
Source (OpenEdge)
Target (MSS)
Default Conversion
DATE
DATE
Straight copy of date portion.
DATE
DATETIME
DATETIME2
Straight copy of date portion. Time set to midnight up to millisecond precision (00:00:00.000).
DATE
SMALLDATETIME
Straight copy of date portion. Time set to midnight up to 1 minute of accuracy.
DATE
DATETIMEOFFSET
Straight copy of date portion. Time set to midnight up to millisecond precision (00:00:00.000). Time zone set to the OpenEdge client session time zone context.
DATETIME
DATE
Straight copy of date portion of DATETIME value. Time and time zone portion are dropped.
DATETIME
DATETIME
DATETIME2
Straight copy of the datetime portion up to millisecond precision.
DATETIME
SMALLDATETIME
Straight copy of the datetime portion up to a minute of precision.
DATETIME
TIME
Straight copy of time portion up to millisecond precision. Date portion is dropped.
DATETIME
DATETIMEOFFSET
Straight copy of the datetime portion. Time zone set to the OpenEdge client time zone.
DATETIME-TZ
DATETIME
DATETIME2
Straight copy of the datetime portion of DATETIME-TZ value (in client time-zone context) up to millisecond precision. Time zone portion is dropped.
DATETIME-TZ
SMALLDATETIME
Straight copy of the datetime portion of DATETIME-TZ value (in client time-zone context) up to 1 minute of precision. Second and millisecond portion is dropped. Time zone portion is dropped.
DATETIME-TZ
DATETIMEOFFSET
Straight copy with millisecond precision.
DATETIME-TZ
DATE
Straight copy of date portion of DATETIME-TZ value (in client time-zone context). Time and time zone portion are dropped.
The following table provides details on converting ABL DATETIME data types and MS SQL Server data types:
Table 14. Converting ABL datetime types to MS SQL Server data types
Source (MSS)
Target (OpenEdge)
Default Conversion
DATE
DATE
Straight copy of date portion.
SMALLDATETIME
DATETIME
DATETIME2
DATE
Straight copy of date portion. Time portion is dropped.
DATETIMEOFFSET
DATE
Receives MSS DATETIMEOFFSET (with time zone context from the database). DataServer converts to OE client time zone context. Date portion of converted timestamp is copied. Time and time zone portions are dropped.
DATE
DATETIME
Straight copy of the date portion into the DATETIME. Time portion is set to 00:00:00
DATETIME
DATETIME2
DATETIME
Straight copy of datetime portion up to millisecond level accuracy.
SMALLDATETIME
DATETIME
Straight copy of datetime portion up 1 minute of accuracy. Second and millisecond portion set to 0.
DATETIME
DATETIME
Straight copy with millisecond accuracy.
DATETIMEOFFSET
DATETIME
Receives MSS DATETIMEOFFSET with stored time zone context. DataServer converts to OE client time zone context. Timestamp portion (in client time) is copied with millisecond accuracy. Time zone portion is dropped.
TIME
DATETIME
Straight copy of TIME portion up to millisecond accuracy. Date portion is set to TODAY based on client time zone context.1
DATE
DATETIME-TZ
Straight copy of the date portion into the DATETIME-TZ. Time portion is set to 00:00:00. Time zone will be set to client time zone context.
DATETIME
DATETIME2
DATETIME-TZ
Straight copy of the date and time portions into the DATETIME-TZ up to millisecond of accuracy. Time zone will be set to client time zone context.
SMALLDATETIME
DATETIME-TZ
Straight copy of the date and time portions of the MSS SMALLDATETIME with 1 minute accuracy. Time zone will be set to client time zone context.
DATETIMEOFFSET
DATETIME-TZ
Straight copy with millisecond accuracy.
1 When a TIME data type in MS SQL Server is mapped to an OpenEdge Datetime data type, the date portion defaults to TODAY's date unless a valid date is specified for the -Dsrv PRGRS_DATEPART_OVERRIDE switch at connection time. This switch allows the user to set a date for this conversion other than TODAY's date.