Date formatting in a where statement

[Dogo23]

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

[Reeza · Accepted solution]

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)

[Dogo23]

Perfect! thank you

[ballardw]

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.

[Tom]

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 ;