topic Why isn't this query returning any records? in SAS Procedures

Why isn't this query returning any records?

MustafaAbdelhaq — Tue, 26 May 2015 14:12:08 GMT

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

Re: Why isn't this query returning any records?

RW9 — Tue, 26 May 2015 14:19:48 GMT

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);

Re: Why isn't this query returning any records?

ballardw — Tue, 26 May 2015 14:44:37 GMT

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.

Re: Why isn't this query returning any records?

MustafaAbdelhaq — Tue, 26 May 2015 16:22:10 GMT

It worked perfectly, thank you so much.