Hi,
I'mt rying to select a date range from a table that currently has dates in the format: 18NOV2016:14:31:52.000000
I tried using where date >= '01JAN2017'd. However, , my result still show records with dates in prior years.
Any suggestions?
Thanks,
Ben
That's a datetime not a date.
Use DATEPART() to obtain the date portion or use DHMS() to create a datetime variable with the date you're using.
where datepart(date) >= '01JAN2017'd
where date >= dhms( '01JAN2017'd, 0, 0, 0)
That's a datetime not a date.
Use DATEPART() to obtain the date portion or use DHMS() to create a datetime variable with the date you're using.
where datepart(date) >= '01JAN2017'd
where date >= dhms( '01JAN2017'd, 0, 0, 0)
Perfect! thank you
The example value you show is a datetime which uses seconds for measure, dates use days, so the comparison doesn't quite work.
You might want
where datepart(date) >= '01JAN2017'd
the function datepart extracts the date portion of the datetime value for use with date comparisons or date functions.
When you ask it to keep datetime values that are after about 6 am on January 1st, 1960 then you are very likely to find values that are before January 2017.
56 data _null_; 57 do num = '18NOV2016:14:31:52.000000'dt , '01JAN2017'd ; 58 put / num= ; 59 length format $20 value $32 ; 60 do format='comma32.','date11.','datetime20.' ; 61 value=putn(num,format); 62 put format $20. +1 value ; 63 end; 64 end; 65 run; num=1795098712 comma32. 1,795,098,712 date11. *********** datetime20. 18NOV2016:14:31:52 num=20820 comma32. 20,820 date11. 01-JAN-2017 datetime20. 01JAN1960:05:47:00
Use a datetime literal to test datetime values. Or convert the values to dates if you want to compare to dates.
where datepart(date) >= '01JAN2017'd ;
where date >= '01JAN2017:00:00'dt ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.