CODEWITHSUNDEEP
sqloracle-databasedatetime
Siphali
Siphali

Reputation: 41

Query to update date only without timestamp

I have millions of records which have date as 06-OCT-20 with some timestamp for which I want to update to 30-SEP-20 with the same timestamp Timestamp should remain same but need to change the date only .

Example as below

"06-OCT-20 06.30.04" to "30-SEP-20 06.30.04" , "06-OCT-20 05.29.04" to "30-SEP-20 05.29.04" , "06-OCT-20 00.28.04" to "30-SEP-20 00.28.04" , "06-OCT-20 09.45.04" to "30-SEP-20 09.45.04" , "06-OCT-20 07.30.04" to "30-SEP-20 07.30.04" ,

in Oracle.

Upvotes: 0

Views: 739

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If you only want to modify values which are on October 6th, and make them have the same time but on September 30th, then you can subtract 6 days from them:

update your_table
set some_date = some_date - interval '6' day
where some_date >= date '2020-10-06'
and some_date < date '2020-10-07';

The filter restricts the update to values on that day.

db<>fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method is:

update t
    set date = timestamp '2020-09-30 06:30:04'
    where date = timestamp '2020-10-06 06:30:04';

Upvotes: 1

Related Questions