My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.