CODEWITHSUNDEEP
oracle-database
AlexGH
AlexGH

Reputation: 2805

Update time part only of Date field

I have a table with some columns of Date datatype. I need to keep the Date part as it is, but I need to format the time part to 00:00:00 for all of them. For example:

09-FEB-14 09:00:00
10-MAR-12 12:00:00

I need to update them to:

09-FEB-14 00:00:00
10-MAR-12 00:00:00

How can I do this?

Upvotes: 1

Views: 45

Answers (1)

MT0
MT0

Reputation: 167972

Since you appear to want to truncate the time part back to midnight then you can just use the TRUNC function:

UPDATE your_table
SET date_column = TRUNC( date_column )

If, instead, you want to set them to a specific time then you can use TRUNC and add an INTERVAL literal:

UPDATE your_table
SET date_column = TRUNC( date_column ) + INTERVAL '12:34:56' HOUR TO SECOND;

If you want to take an hour off the time then you can subtract an INTERVAL literal:

UPDATE your_table
SET date_column = date_column - INTERVAL '1' HOUR;

or subtract a fraction of a day:

UPDATE your_table
SET date_column = date_column - 1/24;

Upvotes: 4

Related Questions