SQL Server 2008 dates are read in as character when using SAS/ACCESS® Interface to ODBC


SQL Server 2008 introduced four new DATETIME data types: DATE, TIME, DATETIMEOFFSET, and DATETIME2. There are also new DATETIME functions. Now you should use the Microsoft SQL Server Native Client 10 ODBC Driver to read data from SQL Server 2008 tables. Generally, the Microsoft driver is installed when you install SQL Server 2008 or the SQL Server tools.

If the SQL Server 2008 table is accessed with the SQL Server ODBC Driver, the dates are read as character strings.

In SQL Server 2005, there is no data-specific data type to store only a date. You must use the DATETIME or SMALLDATETIME data types, so the SQL Server ODBC Driver can be used successfully.

If you are using SAS/ACCESS® Interface to OLE DB to access a SQL Server 2008 server, see SAS KB0036224.

If the DATETIMEOFFSET variable comes in as character, you can use the following code to convert it to a DATETIME variable:

data new;
set x.your_table ;
format dateoff2 datetime21.;
dateoff2=input(your_date_variable, anydtdtm21.) + input(scan(your_date_variable,3,' '),time.);
run;