Use Microsoft SQL Server DataServer

Working with MS SQL Server and ABL datetime data types

Save PDF

Working with MS SQL Server and ABL datetime data types

Save PDF

Last Updated: January 17, 2024
3 minute read

OpenEdge
Version 12.8
Documentation

The following table defines the MS SQL Server 2000, 2005 and 2008 data types and identifies compatible ABL data types.

Table 1. 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.	`DATE`¹ `CHAR` `DATETIME`²^, ³ `DATETIME-TZ`³
`SMALLDATETIME`	Provides date and time data with accuracy to the minute.	`DATE`¹ `CHAR` `DATETIME`²^, ⁴ `DATETIME-TZ`⁴

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 1.

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 2. 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.	`DATE`⁵ `CHAR` `DATETIME`⁵^, ⁶ `DATETIME-TZ`⁶
`TIME`	Provides time of day based on 24-hour clock.	`CHAR`⁵ `DATETIME`⁶
`DATETIME2`	Provides variable precision of up to 100 nanoseconds.	`DATE` `CHAR` `DATETIME`⁵^, ⁶^, ⁷ `DATETIME-TZ`⁶^, ⁷
`DATETIMEOFFSET`	Provides date and time data with time zone offset and variable precision of up to 100 nanoseconds.	`DATE` `CHAR` `DATETIME`⁶^, ⁷ `DATETIME-TZ`⁵^, ⁶^, ⁷

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 3. 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` ⁸ `SMALLDATETIME` `DATE`⁹ `DATETIME2` `DATETIMEOFFSET`
`DATETIME` ¹⁰	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.	`DATE`¹¹ `TIME` `SMALLDATETIME`¹¹ `DATETIME2`⁹^, ¹¹ `DATETIME`⁸^, ¹¹ `DATETIMEOFFSET`¹¹
`DATETIME-TZ` ¹²	Variation of `DATETIME` data type with time zone offset.	`DATE`¹¹ `SMALLDATETIME`¹¹ `DATETIMEOFFSET`⁹^, ¹¹ `DATETIME2`¹¹ `DATETIME`⁸^, ¹¹

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

² 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.

³ 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.

⁴ 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.

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

⁶ 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.

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

⁸ 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.

⁹ 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.

¹⁰ Compatible with MS SQL Servers 2000 and later.

¹¹ 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.

¹² Compatible with MS SQL Server 2008 and later.