DATA Step, Macro, Functions and more

datetime20. format filtering in where clause

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

datetime20. format filtering in where clause

 

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.


Accepted Solutions
Solution
‎05-26-2016 11:43 AM
Super User
Posts: 11,343

Re: datetime20. format filtering in where clause

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

View solution in original post


All Replies
Solution
‎05-26-2016 11:43 AM
Super User
Posts: 11,343

Re: datetime20. format filtering in where clause

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 359 views
  • 0 likes
  • 2 in conversation