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


SQL Server 2008 introduced four new DATETIME data types: DATE, TIME, and DATETIME2. There are also new DATETIME functions. You should use the Microsoft OLE DB Driver for SQL Server Provider (MSOLEDBSQL) to read data from SQL Server 2008 and newer tables. Generally, the OLE DB Provider is installed when you install SQL Server or the SQL Server tools.

If you access the SQL Server 2008 table with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB.1), 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 Microsoft OLE DB Provider for SQL Server (SQLOLEDB.1) can be used successfully.

This is a sample LIBNAME statement using the OLE DB engine, specifying the Microsoft OLE DB Driver for SQL Server Provider, with SQL Server authentication:

libname mylib oledb init_string="Provider=MSOLEDBSQL;
Password=SQLpwd;Persist Security Info=True;
User ID=SQLuser;Initial Catalog=mydatabase;
Data Source=MyServer";

If you are using SAS/ACCESS® Interface to ODBC to access a SQL Server 2008 server, see SAS KB0036229.