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 : Working with MS SQL Server and ABL datetime data types
 
Working with MS SQL Server and ABL datetime data types
The following table defines the MS SQL Server 2000, 2005 and 2008 data types and identifies compatible ABL data types.
Table 10. MS SQL Server 2000 and 2005 datetime data types
MS SQL Server data type
Description
Compatible OpenEdge data types
DATETIME
Provides date and time data to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007.
DATE1
CHAR
DATETIME2, 3
DATETIME-TZ4
SMALLDATETIME
Provides date and time data with accuracy to the minute.
DATE5
CHAR
DATETIME6, 7
DATETIME-TZ8
1 Default, compatible data type to which the native type can be converted.

2 Check the Default to OpenEdge DATETIME option from Pre-Selection Criteria For Schema Pull on the Update/Add Table Definitions dialog to get this data type instead of the default.

3 When converting OpenEdge DateTime/TZ data types to the legacy MS SQL Server DateTime data type, the same 3.33 millisecond accuracy that SQL Server uses is applied to OpenEdge values with millisecond precision. This means that .989, .990 and .991 round to .990, .992, .993 and .994 round to .993, .995, .996, .997 and .998 round to .997 and .999 rounds up to 1.000.

4 When converting OpenEdge DateTime/TZ data types to the legacy MS SQL Server DateTime data type, the same 3.33 millisecond accuracy that SQL Server uses is applied to OpenEdge values with millisecond precision. This means that .989, .990 and .991 round to .990, .992, .993 and .994 round to .993, .995, .996, .997 and .998 round to .997 and .999 rounds up to 1.000.

5 Default, compatible data type to which the native type can be converted.

6 Check the Default to OpenEdge DATETIME option from Pre-Selection Criteria For Schema Pull on the Update/Add Table Definitions dialog to get this data type instead of the default.

7 When converting OpenEdge DateTime/TZ data types to the legacy MS SQL Server SmallDateTime data type, the same minute accuracy that SQL Server uses is applied to OpenEdge values to with second or greater precision. This means that seconds values up to 29.998 seconds will round down to zero and values from 29.999 seconds and up will round up to the next minute.

8 When converting OpenEdge DateTime/TZ data types to the legacy MS SQL Server SmallDateTime data type, the same minute accuracy that SQL Server uses is applied to OpenEdge values to with second or greater precision. This means that seconds values up to 29.998 seconds will round down to zero and values from 29.999 seconds and up will round up to the next minute.

Beginning in OpenEdge Release 10.2B, the DataServer for MS SQL Server supports mappings of MS SQL Server datetime data types DATETIME and SMALLDATETIME to OpenEdge data type DATETIME-TZ along with the compatible OpenEdge types listed in Table 9.
MS SQL Server 2008 offers the DATETIMEOFFSET data type to support time zones in a datetime column as well as three other datetime data types. The following table defines the MS SQL 2008 data types and identifies compatible ABL data types.
Table 11. MS SQL Server 2008 datetime data types
MS SQL Server data type
Description
Compatible OpenEdge data types
DATE
Provides day of the year based on the Gregorian calendar.
DATE1
CHAR
DATETIME2, 3
DATETIME-TZ4
TIME
Provides time of day based on 24-hour clock.
CHAR5
DATETIME6
DATETIME2
Provides variable precision of up to 100 nanoseconds.
DATE
CHAR
DATETIME7, 8, 9
DATETIME-TZ10, 11
DATETIMEOFFSET
Provides date and time data with time zone offset and variable precision of up to 100 nanoseconds.
DATE
CHAR
DATETIME12, 13
DATETIME-TZ14, 15, 16
1 Default data type mapping for this MS SQL Server data type.

2 Default data type mapping for this MS SQL Server data type.

3 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

4 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

5 Default data type mapping for this MS SQL Server data type.

6 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

7 Default data type mapping for this MS SQL Server data type.

8 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

9 ABL data type limits MSS data type accuracy to millisecond precision.

10 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

11 ABL data type limits MSS data type accuracy to millisecond precision.

12 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

13 ABL data type limits MSS data type accuracy to millisecond precision.

14 Default data type mapping for this MS SQL Server data type.

15 New MSS data types with a time component support fractional accuracy up to 100 nanoseconds. Hence, when we receive any time data from an OpenEdge datetime data type with a time component, the fractional portion of the time value greater than millisecond accuracy is dropped in the conversion. For example, a time value '12:00:00.1235678' in MS SQL Server would yield a resultant value of '12:00:00.123' in an OpenEdge DATETIME column. NOTE: The time component is truncated rather than rounded in order to be consistent with MS SQL Server which also truncates the value when converting between two datetime data types defined with inconsistent fractional accuracy.

16 ABL data type limits MSS data type accuracy to millisecond precision.

Note: Date time related data types of MS SQL Server 2008 will work only with the SQL Native Client10 (SNAC 10.0) driver or above, and not with earlier native and non-native drivers, as earlier version drivers will not support 2008 date time data types.
The ABL DATETIME and DATETIME-TZ data types are compatible with datetime data types supported by MS SQL Server. The following table provides details on compatibility:
Table 12. ABL datetime data types
ABL Data Type
Definition
MS SQL Server compatible data type
DATE
ABL date contains only a calendar date component and there is no time zone element to it.
DATETIME 1
SMALLDATETIME
DATE2
DATETIME2
DATETIMEOFFSET
DATETIME 3
The DATETIME data type consists of two parts, one an ABL date and one an ABL time. The unit of time is milliseconds from midnight.
DATE4
TIME
SMALLDATETIME5
DATETIME26, 7
DATETIME8, 9
DATETIMEOFFSET10
DATETIME-TZ 11
Variation of DATETIME data type with time zone offset.
DATE12
SMALLDATETIME13
DATETIMEOFFSET14, 15
DATETIME216
DATETIME17, 18
1 Default and backward-compatible, data type mapping used for an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is checked.

2 Default data type mapping on an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is unchecked. NOTE: You should only uncheck this box when your target database is MS SQL Server 2008 or later.

3 Compatible with MS SQL Servers 2000 and later.

4 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

5 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

6 Default data type mapping on an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is unchecked. NOTE: You should only uncheck this box when your target database is MS SQL Server 2008 or later.

7 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

8 Default and backward-compatible, data type mapping used for an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is checked.

9 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

10 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

11 Compatible with MS SQL Server 2008 and later.

12 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

13 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

14 Default data type mapping on an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is unchecked. NOTE: You should only uncheck this box when your target database is MS SQL Server 2008 or later.

15 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

16 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.

17 Default and backward-compatible, data type mapping used for an OpenEdge migration to the MS SQL Server DataServer when the Map to MSS Datetime Type check box is checked.

18 Since the valid date range for OpenEdge date and datetime data types with a date component is wider than the range available to MS SQL Server data types with a date component, it is recommended that a validation expressions be applied to fields in the schema holder mapped to MS SQL Server datetime types with a narrower date range. This would ensure that the client first blocked out-of-range dates before they were invalidated by server operations.