datetime20. format filtering in where clause

ashwini2 — Tue, 24 May 2016 17:14:02 GMT

proc sql;
select distinct MINOFTRANSFER_EFF_DT
from HSD.master_table
where MINOFTRANSFER_EFF_DT between '01APR2016'd and '30JUN2016'd
;
quit;

I am trying to filter data based on the date and it returns no values.

MINOFTRANSFER_EFF_DT is in datetime20. format.

Re: datetime20. format filtering in where clause

ballardw — Tue, 24 May 2016 17:17:47 GMT

You are comparing a Datetime to Date values. Try

where Datepart (MINOFTRANSFER_EFF_DT) between '01APR2016'd and '30JUN2016'd

to compare the date of the datetime