CODEWITHSUNDEEP
sqloracle-database
user11829790
user11829790

Reputation:

How to create update query to set the current date minus few days in the table?

I am trying to update date in table

Selected data from my_table

SELECT * 
FROM my_table 
WHERE date_time > to_date('25/01/2017', 'MM/DD/YYYY')
  AND date_time < to_date('15/09/2017', 'MM/DD/YYYY')

Result:

  id  | date_time
------+-------------------------------
20873 | 11-SEP-17 02.44.36.953000000 PM
20874 | 14-FEB-17 11.02.32.307000000 AM
20872 | 31-JAN-17 11.50.09.406000000 AM
20871 | 26-JAN-17 12.43.06.868000000 PM

Now I want to set the result in date_time column to result - 3 days.

So result after update will be:

  id  | date_time
------+-------------------------------
20873 | 08-SEP-17 02.44.36.953000000 PM
20874 | 11-FEB-17 11.02.32.307000000 AM
20872 | 28-JAN-17 11.50.09.406000000 AM
20871 | 23-JAN-17 12.43.06.868000000 PM

I am trying something like that:

SELECT id, date_time - 3 
FROM my_table 
WHERE date_time > to_date('25/01/2017', 'MM/DD/YYYY')
  AND date_time < to_date('15/09/2017', 'MM/DD/YYYY')

But the result is now without time ...

Upvotes: 1

Views: 2762

Answers (4)

Kiran Patil
Kiran Patil

Reputation: 339

Use Bellow Query..

SELECT id, to_char(date_time-3,'MM/DD/YYYY HH:MM:SS')
FROM my_table 
WHERE date_time > to_date('25/01/2017', 'MM/DD/YYYY')
  AND date_time < to_date('15/09/2017', 'MM/DD/YYYY')

UPDATE Your table 

SET  date_time = to_char(date_time-3,'MM/DD/YYYY HH:MM:SS')
WHERE date_time > TO_DATE('25/01/2017', 'MM/DD/YYYY')
AND date_time < TO_DATE('15/09/2017', 'MM/DD/YYYY')

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

You can substract three days by using interval '-3' day

update my_table t
   set t.date_time = t.date_time + interval '-3' day;

Demo

Upvotes: 3

Alexander S.
Alexander S.

Reputation: 2279

Use 

DATEADD(DD, -3,date_time) --FOR MSSQL
TO_DATE(date_time, 'MM/DD/YYYY') -3 --FOR Oracle / PLSQL

Try this before you do your update:

SELECT id, date_time, TO_DATE(date_time, 'MM/DD/YYYY') -3 as new_date_time
FROM my_table 
WHERE date_time > TO_DATE('25/01/2017', 'MM/DD/YYYY')
AND date_time < TO_DATE('15/09/2017', 'MM/DD/YYYY')

To Update:

UPDATE my_table 
SET  date_time = TO_DATE(date_time, 'MM/DD/YYYY') -3
WHERE date_time > TO_DATE('25/01/2017', 'MM/DD/YYYY')
AND date_time < TO_DATE('15/09/2017', 'MM/DD/YYYY')

Upvotes: 0

JayD
JayD

Reputation: 69

For SQL Server, you could use:

update #my_table set date_time = dateadd(day, -3, date_time) ;

I just used #my_table (for temporary table) instead of your my_table. Syntax is different for Teradata but you would need to specifcy required database for specific answer.

Upvotes: 0

Related Questions