The TODAY() and DATEPART() functions might return a DATETIME rather than a DATE variable when you query a Database Management System (DBMS) table


Some DBMSs, including SQL Server, Oracle, Sybase, and Sybase IQ, do not have the equivalent of SAS DATE variables. In these DBMSs, the DATE variable also contains a TIME variable, so they are equivalent to SAS DATETIME variables.

When an SQL procedure (PROC) query contains the TODAY() or DATEPART() function, the result should be a DATE variable if the query was processed by SAS. However, for those DBMSs that do not have an equivalent DATE variable, a DATETIME or CHARACTER variable is returned to SAS by that DBMS.

Take for example the following query:

proc sql;
select distinct today() as mydate from mydblib.test;
quit;

Running this query results in incorrect data for the variable mydate. The variable will be a DATETIME for queries where the libref MYDBLIB was pointed to a SQL Server or Oracle DBMS and a CHARACTER if the DBMS was Sybase IQ.

SQL Server: (DATETIME22.3)
mydate
----------------------
18SEP2012:00:00:00.000
Oracle: (DATETIME20.)
MYDATE
----------------------
18SEP2012:00:00:00
SybaseIQ: (Char)
mydate
--------
2012-09-18
WARNING: Wrong type of format for data type: DATE
Sybase: (DATETIME22.3)
mydate
----------------------
18SEP2012:00:00:00.000

A problem will result if later code treats that variable as a DATE rather than as a DATETIME. The only workaround is to turn off implicit pass-through so that the function is not passed to the DBMS and is instead processed by SAS. This can be done by setting the DIRECT_SQL=NO LIBNAME option.