Oracle Add 1 Hour in SQL

Oracle Add 1 hour in SQL

select sysdate + 1/24 from dual;

sysdate is a function without arguments which returns DATE type

+ 1/24 adds 1 hour to a date

select to_char(to_date('2014-10-15 03:30:00 pm', 'YYYY-MM-DD HH:MI:SS pm') + 1/24, 'YYYY-MM-DD HH:MI:SS pm') from dual;

SQL add 2 hour to a column with type date

I prefer using interval for this:

select deliverydate + interval '2' hour
from the_table;

The above is standard SQL and works well in Oracle.

More details in the manual: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00221

How to add hours to date in 24 hours format

Your first line converts a date to a string. You cannot then add 8/24 to it. Do the addition before the conversion:

SELECT to_char(IN_ENDDATE + 8/24.0, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL;

IN_ENDDATE really does need to be a date type to allow +8/24 to work. If it's a timestamp, add it as an interval:

IN_ENDDATE + INTERVAL '8' HOUR

This form might be safer to use for a couple of reasons:

• it works on both date and timestamps
• it's more readable

If IN_ENDDATE is a non-date type (eg varchar) then your query works without the +8/24 because it is being successfully implicitly converted from varchar to date, before being passed to to_char. In this case either be explicit about your conversion:

SELECT to_char(to_date(IN_ENDDATE, 'YYMMDD WHATEVER') + 8/24.0, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL

SELECT to_char(to_date(IN_ENDDATE, 'YYMMDD WHATEVER') + INTERVAL '8' HOUR, 'DD-MM-YYYY HH24:MI:SS')
INTO IN_END_DATE_STRING
FROM DUAL

Or set your IN_ENDDATE parameter to really be a date type

Add two hours to timestamp

You need to change your HOUR TO MINUTE to match the value you're actually passing:

sysdate + INTERVAL '0 02:00:00.0' DAY TO SECOND

You might also want to use systimestamp instead of sysdate. You can use a shorter interval literal too if you're always adding exactly two hours:

systimestamp + INTERVAL '02:00' HOUR TO MINUTE

or just

systimestamp + INTERVAL '2' HOUR

As a quick demo:

SELECT systimestamp, systimestamp + INTERVAL '2' HOUR FROM DUAL;

SYSTIMESTAMP                        SYSTIMESTAMP+INTERVAL'2'HOUR      
----------------------------------- -----------------------------------
11-MAY-15 11.15.22.235029000 +01:00 11-MAY-15 13.15.22.235029000 +01:00

How to add last hour of a day in a Date in Oracle?

Truncate the date value back to midnight (just in case there is a non-midnight time component) and then add an interval of 23:59:59:

SELECT TRUNC( your_date_value ) + INTERVAL '23:59:59' HOUR TO SECOND
         AS last_second_of_day
FROM   your_table;

or add 1 day and subtract 1 second:

SELECT TRUNC( your_date_value ) + INTERVAL '1' DAY - INTERVAL '1' SECOND
         AS last_second_of_day
FROM   your_table;

or add 86399 seconds:

SELECT TRUNC( your_date_value ) + INTERVAL '86399' SECOND
         AS last_second_of_day
FROM   your_table;

or

SELECT TRUNC( your_date_value ) + 86399 / 86400
         AS last_second_of_day
FROM   your_table;

(But using intervals is more explicit as to their meaning whereas using magic numbers may not be clear when you look at the code in 6 months time.)

Then, for the sample data:

CREATE TABLE your_table ( your_date_value ) AS
SELECT DATE '2020-10-14' FROM DUAL UNION ALL
SELECT DATE '2020-01-01' + INTERVAL '12' HOUR FROM DUAL;

The queries all output:

| LAST_SECOND_OF_DAY  |
| :------------------ |
| 2020-10-14 23:59:59 |
| 2020-01-01 23:59:59 |

db<>fiddle here

PL/SQL Adding hours to timestamp parameter

If your parameter is not already a timestamp, use to_timestamp or to_date to convert it:

to_timestamp(dateFrom,'mm/dd/yyyy hh24:mi:ss')

(substitute the appropriate mask based on the format of your input parameter)

Then just add 7/24.

to_timestamp(dateFrom,'mm/dd/yyyy hh24:mi:ss') + 7/24;

Adding 1 adds a full day, so adding 1/24 adds 1 hour.

This can also be done with the INTERVAL operator:

to_timestamp(dateFrom,'mm/dd/yyyy hh24:mi:ss') + INTERVAL '7' hour

---

Related Topics