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