DATETIME-TZ types are not converted as expected by the "Generate Delta.sql OpenEdge to MSS..." utility.

DATETIME-TZ types are not converted as documented if the "Map to MSS 'Datetime' Type" option is enabled by the "Generate Delta.sql OpenEdge to MSS..." utility.

Sep 24, 2018
Knowledge

Title

URL Name

000045812

Article Number

000165510

Environment

Product: OpenEdge Version: 10.2B, 11.x OS: Windows Other: Microsoft SQL Server 2008 and later, SQL Server Native Client 10

Question/Problem Description

DATETIME-TZ types are not converted if the "Map to MSS 'Datetime' Type" option is enabled, when using the "Generate Delta.sql OpenEdge to MSS..." utility.

The resulting .sql and .e files contain the following information:

/* .sql */

-- ** ALTER TABLE dbo.customer ADD

-- ** custmodified UNSUPPORTED

-- ** go

/* .e */

customer had columns with unsupported data types.

The sql was commented out and no information was

outputed to the OpenEdge DF File.

Where the custmodified field is defined with type DATETIME-TZ.

The fields are omitted entirely from the generated delta .df file.

If "Map to MSS 'Datetime' Type" is unchecked, then DATETIME-TZ fields are processed and converted to the DATETIMEOFFSET data-type.

Steps to Reproduce

Clarifying Information

According to table 12 on page 69 of the OpenEdge 11.3 DataServer for MS SQL Server manual, DATETIME-TZ should convert to DATETIMEOFFSET if "Map to MSS 'Datetime' Type" is selected.

Per the same table, if "Map to MSS 'Datetime' Type" is not selected then DATETIME-TZ fields should be converted to the DATETIME type.

Error Message

Defect Number

Defect PSC00286198

Enhancement Number

Cause

This is expected behavior. The feature is described incorrectly in the documentation.
By design "Map to MSS 'Datetime' Type" is checked by default. This means that OpenEdge Date and Datetime types map to legacy MSS Datetime and Smalldatetime data types.
To use new date types introduced in Microsoft SQL Server 2008, then "Map to MSS 'Datetime' Type" should be unchecked.

Resolution

To use new SQL Server date types in conversion, uncheck "Map to MSS ' Datetime ' Type".
To use legacy SQL Server date type conversions, check the "Map to MSS ' Datetime ' Type" option (default).

Workaround

Notes

"Map to MSS ' Datetime ' Type" resolves to the MAPMSSDATETIME variable in batch.

References to other documentation:

OpenEdge® Data Management: DataServer for Microsoft® SQL Server

Keyword Phrase

Last Modified Date

9/24/2018 5:00 PM

DATETIME-TZ types are not converted as expected by the "Generate Delta.sql OpenEdge to MSS..." utility.

Files

Was this article helpful?

Was this article helpful?

DATETIME-TZ types are not converted as expected by the "Generate Delta.sql OpenEdge to MSS..." utility.

Information

Related Articles