DATA Step, Macro, Functions and more

DATETIME format help

Reply
Frequent Contributor
Posts: 94

DATETIME format help


How to Query date which is in format datetime20. (table1) in the proc sql case statement (Joined two tables)

case when test.log = "01MAY2015"d  then '201505'

 

Table 1 date format is datetime20.

Table 2 date format is yymmmdd10.

Super User
Posts: 24,028

Re: DATETIME format help

Posted in reply to Kalai2008
Rather than if/then consider using an approriate format instead. Look at DATEPART to create a DATE variable from a DATETIME variable
Valued Guide
Posts: 540

Re: DATETIME format help

Posted in reply to Kalai2008

The date formats are only relevant in presentation of your data. But they do tell you what the numbers mean (seconds since 01JAN1960:00:00:00 or days since 01JAN1960. Internally they are stored as a number. But they are the same values no matter what date or datetime format is associated. As a consequence a date can always be queried using a date literal like '02NOV2017'd or a datetime like ' 02JAN2017:00:00:00'dt. If you want to compare a datetime to a date use the datepart() function: WHERE datepart(dtvar)='02NOV2017'd.  In a join that could be WHERE datepart(dtvar)=datevar.

 

Hope this helps,

- Jan.

Ask a Question
Discussion stats
  • 2 replies
  • 152 views
  • 2 likes
  • 3 in conversation