Why isn't this query returning any records?

[MustafaAbdelhaq]

Hello,

I have a table which contains multiple columns, one of the columns is call Date_Of and it contains dates in the following format. ddMONyyy:hh:mm:ss Ex. 26MAY2015:00:00:00.

I'm trying to use a query in which I try to extract only records of today.

   SELECT t12.DATE_OF,

      FROM QUERYt12

      WHERE t1.DATE_OF<=today();

QUIT;

Unfortunately the query doesn't extract any records, on the other hand if I switch the < operator to > it extracts all the data.

I think it's related to the format of the date, I could't find a solution to this problem, hope someone will be able to help me out here.

Appreciate your help.

Thank you

[RW9 · Accepted solution]

Assuming t1.DATE_OF is a proper numeric data time value, then it includes date and time.  Today() function returns a date value, i.e. no time.

If you only want to compare dates then:

where datepart(T1.DATE_OF) <= today();

If you want time as well then you need to build a datetime response:

where T1.DATE_OF) <= dhms(today(),hour(time()),minute(time),0);

[MustafaAbdelhaq]

It worked perfectly, thank you so much.

[ballardw]

As to the why: Today() returns a number of days since 1 Jan 1960 as are all SAS DATE valued variables. If your variable actually contains a DATETIME value as shown then the value stored is the number of SECONDS.