Date Column From Sqlserver 2008 Converted to NVARCHAR(10) Via DG4ODBC (Doc ID 1383244.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)
*** reviewed for currency on 08-Sep-204 ***

Symptoms

Selecting DATE data type column from SQL Server 2008 via Oracle Database gateway for ODBC (DG4ODBC) returns inconsistent output.

SQL> select datop from  dbo.current_flightlegs@todt where fltid='SK 0400';

DATOP
--------------------
2010-09-14
2010-09-20
2010-09-21
2011-05-04

SQL> select datop from  dbo.current_flightlegs@todt where fltid='SK 0400' and datop < '2011-05-04';

DATO
----
2010
2010

SQL> select datop from  dbo.current_flightlegs@todt where fltid='SK 0400' and datop = '2011-05-04';

no rows selected



Furthermore DATE column is translated to NVARCHAR(10).

If on SQL Server, I create the table myTest :

CREATE TABLE [dbo].[MyTest]
([FLTID] [char](8) NOT NULL,
[DATOP] [date] NOT NULL,
[DEP_STN] [char](3) NOT NULL,
[ARR_STN] [char](3) NOT NULL,
[STD] [smalldatetime] NULL,
[STA] [smalldatetime] NULL,
[ETD] [smalldatetime] NULL
)



I get :

SQL> describe "dbo"."MyTest"@todt;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FLTID                                     NOT NULL CHAR(8)
DATOP                                     NOT NULL NVARCHAR2(10)
DEP_STN                                   NOT NULL CHAR(3)
ARR_STN                                   NOT NULL CHAR(3)
STD                                                DATE
STA                                                DATE
ETD                                                DATE

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms