CODEWITHSUNDEEP
oracle-database
M.Nehru
M.Nehru

Reputation: 41

How to change the Hour value stored in Oracle Date variable?

I need to change the hour value of data stored in the Date variable.Is this possible in Oracle ?

Upvotes: 2

Views: 7059

Answers (1)

MT0
MT0

Reputation: 167784

Oracle Setup

CREATE TABLE your_table ( value DATE );

INSERT INTO your_table VALUES ( SYSDATE );

Add one hour to the date:

UPDATE your_table
SET value = value + INTERVAL '1' HOUR;

Change the hour to 8am (without changing the year/month/day/minute/second):

UPDATE your_table
SET value = value + NUMTODSINTERVAL(
                      8 - EXTRACT( HOUR FROM CAST ( value AS TIMESTAMP ) ),
                      'HOUR'
                    );

To set the time to a new time:

Change the time from the current value to 14:56:27:

UPDATE your_table
SET value = TO_DATE(
              TO_CHAR( value, 'yyyy-mm-dd' ) || ' 14:56:27',
              'yyyy-mm-dd hh24:mi:ss'
            )

or

UPDATE your_table
SET value = TRUNC( value ) + INTERVAL '14:56:27' HOUR TO SECOND;

Upvotes: 3

Related Questions