How to subtract hours from datetime Variable in proc sql

[pp2014]

I have datetime variable.  For example:  start_date = '07JUN2014:00:54:00"  

I want to subtract 3 hours from the above date in  PROC SQL.

So output will be 06JUN2014:09:54:00

Can anybody help me in this?

[PaigeMiller]

Use the INTNX function

[ballardw]

The function INTNX is used to change date and time values

intnx('hour', start_date,-3) should do it. You don't say if you want to create a new variable or overwrite the existing.

[PGStats]

If you want to keep the same minutes and seconds, you must specify "SAME" as the alignment argument to INTNX:

data _null_;

start_date = '07JUN2014:00:54:00'dt;

new_date1 = intnx("HOUR",start_date,-3);

new_date2 = intnx("HOUR",start_date,-3,"SAME");

put (start_date new_date1 new_date2) (:datetime21. /);

run;

07JUN2014:00:54:00

06JUN2014:21:00:00

06JUN2014:21:54:00

PG

[pp2014]

Thanks.

If I have date in the following format (data is in Oracle database) 

start_date = '6/7/2014 12:54:00 AM'

How do I subtract 3 hours to get '6/6/2014  9:54:00 PM'?

[PGStats]

I don't do Oracle.

[PGStats]

... But, if you bring in your date as a string, you could do as follows:

data _null_;

dtxt =  '6/7/2014 12:54:00 AM';

start_date = input(dtxt, anydtdtm.);

new_date = intnx("HOUR",start_date,-3,"SAME");

put new_date :dateAMPM20.;

run;

06JUN14:09:54:00 PM

Or in terms of SQL

select intnx("HOUR", input(OracleTextDate, anydtdtm.), -3, "SAME") as new_date, ...

PG

[pp2014]

Thanks for help...

[Tom]

DATETIME and TIME values are both stored as number of seconds.

START_DATE - '03:00't

[Ksharp]

START_DATE - 3*60*60