Specifying a DATE comparison in a WHERE clause when querying a Database Management System (DBMS) table might result in unexpected output. The problem occurs because some databases do not have the equivalent of a SAS DATE variable. A DBMS DATE variable includes the TIME portion and is the equivalent of a SAS DATETIME variable. Trying to compare a DATE constant against a DATETIME in Base SAS^® would not, in most cases, result in a match. The reason for this is that the comparison would be done against the internal value. That is, the value that is stored by SAS in the variable. However, if the DBMS is doing the comparison, the date constant is compared against the DATE variable with a time of midnight.

With the LIBNAME engine, if the entire SAS query can be passed to the DBMS for processing, then performing a comparison of a DATE constant and a DBMS DATE variable (which includes a time portion) would generate results that you would expect. However, if the entire query cannot be passed to the DBMS and it is SAS that does the comparison, it will be a comparison of the DATE constant with what is a DATETIME variable. Because the internal values would be used in the comparison, the output would be different than if the DBMS had done the comparison.

For example, if an Oracle table contains a row with date variable with a value (shown formatted as a SAS DATETIME) of '02JAN2012:00:00:00', performing the following query would return a match of the row in which the hire date equals January 2, 2012:

However, running the following query would not result in a match. SAS would be doing the comparison because the SUBSTR function might prevent the entire query from being passed to Oracle and the comparison of '02JAN2012:00:00:00'DT with '02JAN2012'D would not be seen as equal in SAS.

To work around this issue, do comparisons without a type mismatch.