CODEWITHSUNDEEP
sqlsql-servert-sqlsas
hadoo
hadoo

Reputation: 213

SAS EG DATE/TIME IN WHERE CLAUSE

I need to use a date/timestamp field in my where clause where it is >today()1pm

I'm setting it up to be a scheduled task, so the "today()" part is important I dont know how to combine that with the time piece though all I could come up with was this- and it seems like very inefficent code if it even works

datepart(fielda)=today() and timepart(fielda)>01:00:00pm

I need to be able to say here are the items with a timestamp of "today" after "1pm"

*I am passing my sql to an underlying database.

Upvotes: 1

Views: 1638

Answers (4)

hadoo
hadoo

Reputation: 213

this is so much fancier and is easier to use

datefield between to_date(to_char(sysdate-1,'mm/dd/yyyy') || '12:00:00','mm/dd/yyyy hh24:mi:ss') and to_date(to_char(sysdate,'mm/dd/yyyy') || '08:32:27','mm/dd/yyyy hh24:mi:ss')

this will supply all values from yesterday at 12pm through 832am today

Upvotes: 1

Stu Sztukowski
Stu Sztukowski

Reputation: 12899

Combine a date9-formatted macro variable with the time. This will create a datetime literal. This method is going to be as efficient as can be, since SAS resolves the literal one time only, and can then use any existing index on datestamp.

%let today = %sysfunc(today(), date9.);

proc sql noprint;
    create table selected as
        select *
        from mydata
        where datestamp > "&TODAY.:13:00:00"dt
    ;
quit;

You can confirm the value with the code below.

%put %sysfunc(putn("&TODAY.:13:00:00"dt, mdyampm24.) );

Upvotes: 0

Chris Long
Chris Long

Reputation: 1319

If you're using PROC SQL directly (not pass-through) you can use the SAS dhms function:

proc sql noprint;
  * Assume myData contains a var named datestamp;
  create table selected as
    select * from myData
    where datestamp > dhms(today(), 13, 0, 0)
  ;
quit;

The function constructs a datetime value from the supplied Date, Hour, Minutes and Seconds values, hence dhms.

This won't work if your SQL is being passed through to the underlying database because it's unlikely to understand the dhms function.

Upvotes: 3

Stavr00
Stavr00

Reputation: 3314

For a SQL92 compliant database engine, the where syntax is as follows:

EXTRACT(HOUR FROM fielda) >= 13

Upvotes: -1

Related Questions