If you use a WHERE clause to compare an Oracle date variable with a SAS DATE value using SAS/Access® to Oracle, you may receive incorrect results depending on whether SAS or the database does the subsetting. Technically this doesn't require a WHERE clause but just the fact that it's SAS doing the comparison of the DATETIME variable with a DATE constant. If the database is the one doing the processing the results should be correct as SAS will append 00:00:00 to the end of the date or pass it over such that the database will compare a datetime with a datetime value. If SAS does the processing the results will be incorrect as the date variable will come into SAS as a datetime so SAS will be compare a date with a datetime and the appropriate matches will not be found.

Other than supplying an appropriate constant that matches what the true underlying value is another option is to override the Oracle datetime default using the data set option DBSASTYPE= or SASDATEFMT=.

%let amp='17apr2007:10:34:56'dt;

select *
from oralib.employee
where hiredate = &constant and substr(dept,1,3)='MIS';

or

%let amp='17apr2007'd;

select *
from oralib.employee(sasdatefmt=(hiredate='date9.'))
where hiredate = &constant and substr(dept,1,3)='MIS';