CODEWITHSUNDEEP
oracle-databasedatetimestored-procedures
Mayank Pathak
Mayank Pathak

Reputation: 3681

Alter a datetime in Oracle and set time to 9 am

i have 28-APR-2016 10:05:07 date as parameter in stored procedure. This may be the current time also as string date.

i need to set the time to 9 am to check the shift start timing. 

 SELECT TO_DATE('28-APR-2016 10:05:07', 'DD-MON-YYYY HH24:MI:SS') FROM dual;

I am new to oracle. Help is appreciated.

Upvotes: 0

Views: 2079

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want the date with 9:00 a.m., then you can do:

SELECT TRUNC(TO_DATE('28-APR-2016 10:05:07', 'DD-MON-YYYY HH24:MI:SS')) + 9/24.0
FROM dual;

You can also use:

SELECT TRUNC(TO_DATE('28-APR-2016 10:05:07', 'DD-MON-YYYY HH24:MI:SS')) + INTERVAL '9' HOUR
FROM dual;

I'm just old-fashioned so I tend to use the first method.

Upvotes: 3

Related Questions