How to insert a date field into MS SQL Server using SAS/ACCESS® Interfaceto ODBC


In order to insert a date field into a Microsoft SQL Server table, the date field in SAS must be formatted as mmddyy10.

Here is an example when creating a new MS SQL Server table:

libname sqlodbc odbc dsn=MSsqlserver uid=xxxxxx pwd=yyyyyy;
data old;
input newname mmddyy10.;
format newname mmddyy10.;
cards;
12/12/1984
11/11/1999
9/11/1978
9/10/1963
;
run;

data sqlodbc.new;
set work.old;
run;

If the SQL Server table already exists, then use the SASDATEFMT data set option (the SAS date variable must be formatted as an mmddyy10.). Here is an example,

PROC SQL;
INSERT INTO sqlodbc.EMP(sasdatefmt=(HIREDATE='mmddyy10.'))
SELECT * FROM work.old;
QUIT;