Problem Note 56435: SAS/ACCESS® Interface to ODBC might generate incorrect dates when you use version 14 of the Microsoft SQL Server driver
When using SAS/ACCESS Interface to ODBC with Microsoft SQL Server, you might get incorrect dates of 01JAN1753. The following example shows the problem:
libname anlanldb odbc dsn=your-dsn user=user-ID password=password bulkload=yes;
proc sql;
drop table anlanldb.petetime;
quit;
data anlanldb.petetime;
format c d e datetime23.3;
c='31OCT2015:00:00:00.000'dt;
d='31MAR2015:00:00:00.000'dt;
e='29MAR2015:00:00:00.000'dt;
put c= d= e=;
run;
proc sql ;
select * from anlanldb.petetime;
quit;
You receive the results shown below. (Note the result for variables d and e.)
c d e
31OCT2015:00:00:00.000 01JAN1753:00:00:00.000 01JAN1753:00:00:00.000
The problem occurs with version 14 of the Microsoft ODBC driver and when the option BULKLOAD=YES is specified in the LIBNAME statement.
To circumvent the problem, do one of the following:
- Create a data source name (DSN) from an earlier version of the Microsoft ODBC driver for SQL Server. (Multiple versions can be installed on the same machine.)
- Remove BULKLOAD=YES from the LIBNAME statement. You can use INSERTBUFF=1000 READBUFF=1000 to increase the performance on your LIBNAME statement.
Operating System and Release Information
SAS System | SAS/ACCESS Interface to ODBC | Microsoft® Windows® for x64 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8 Enterprise 32-bit | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8 Enterprise x64 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8 Pro 32-bit | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8 Pro x64 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8.1 Enterprise x64 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8.1 Pro | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows 8.1 Pro 32-bit | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2008 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2008 R2 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2008 for x64 | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2012 Datacenter | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2012 R2 Datacenter | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2012 R2 Std | 9.4_M2 | | 9.4 TS1M2 | |
Microsoft Windows Server 2012 Std | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Enterprise 32 bit | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Enterprise x64 | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Home Premium 32 bit | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Home Premium x64 | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Professional 32 bit | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Professional x64 | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Ultimate 32 bit | 9.4_M2 | | 9.4 TS1M2 | |
Windows 7 Ultimate x64 | 9.4_M2 | | 9.4 TS1M2 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
When using SAS/ACCESS Interface to ODBC with version 14 of the Microsoft SQL Server driver on Windows, you might receive incorrect date values of 01JAN1753. To resolve the problem, either use an earlier version of the Microsoft SQL Server driver or specify BULKLOAD=NO in the LIBNAME statement.
Type: | Problem Note |
Priority: | high |
Date Modified: | 2015-10-05 10:49:01 |
Date Created: | 2015-08-17 12:00:57 |