Subtract one hour from datetime rather than one day

oracle datetime plsql date-arithmetic
lightweight picture lightweight · Apr 18, 2013 · Viewed 36.8k times · Source

I have a datetime column in Oracle (MM/DD/YYYY HH:MM:SS AM/PM) but when I do this:

SELECT MAX(D_DTM)-1 FROM tbl1

...it goes back a day. How do I remove one hour from the column rather than one day?

I've also noticed that the datetime records for 12AM look like MM/DD/YYYY and not MM/DD/YYYY 00:00:00; I'm not sure if that matters.

Answer

A.B.Cade picture A.B.Cade · Apr 18, 2013

Randy's answer is good, but you can also use intervals:

SELECT MAX(D_DTM)- interval '1' hour FROM tbl1

Related questions

Oracle PLSQL truncate datetime to specific hours

I have an Oracle PLSQL code generating a list of datetime stamps and I would like to truncate them to the specific hours of 7am and 7pm rather than the beginning of the day. For example: 01/03/2013 0700 becomes 01/03/2013 0700 01/03/2013 1235 becomes 01/03/2013 0700 01/03/2013 1932 becomes 01/03/2013 1900 02/03/2013 0612 becomes 01/03/2013 1900 …

Read More
How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More
Is java.sql.Timestamp timezone specific?

I have to store UTC dateTime in DB. I have converted the dateTime given in specific timezone to UTC. for that I followed the below code. My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta" My Server/DB(oracle) is …

Read More