Desktop productivity for business analysts and programmers

Date formatting in a where statement

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Date formatting in a where statement

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


Accepted Solutions
Solution
‎12-12-2017 12:11 PM
Super User
Posts: 22,820

Re: Date formatting in a where statement

[ Edited ]

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)

 

 

View solution in original post


All Replies
Solution
‎12-12-2017 12:11 PM
Super User
Posts: 22,820

Re: Date formatting in a where statement

[ Edited ]

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)

 

 

Contributor
Posts: 54

Re: Date formatting in a where statement

Perfect! thank you

Super User
Posts: 13,000

Re: Date formatting in a where statement

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.

Super User
Super User
Posts: 7,844

Re: Date formatting in a where statement

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 ;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 209 views
  • 1 like
  • 4 in conversation