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 ;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.