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

Question

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 ...

Which RDBMS is this for? It often does make a difference whether you're using MySQL, PostgreSQL, Oracle, SQL Server or IBM DB2 - or something else even. Please add a relevant tag to your question! — marc_s, Oct 18 '19 at 09:48
this return only the date part of the timestamp column (and updates so) @FrustratedDeveloper . — Barbaros Özhan, Oct 18 '19 at 10:28
use convert function like CONVERT(date_time, ''yyyy-mm-dd hh:mm:ss'') — Kiran Patil, Oct 18 '19 at 10:31
`CONVERT()` is not a supported function in Oracle @KiranPatil — Barbaros Özhan, Oct 18 '19 at 10:34

Barbaros Özhan · Accepted Answer · 2019-10-18T10:40:44.510

2

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

edited Oct 18 '19 at 10:40

answered Oct 18 '19 at 10:27

Barbaros Özhan

59,113
10
31
55

Alexander S. · Answer 2 · 2019-10-18T10:30:54.060

0

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')

edited Oct 18 '19 at 10:30

answered Oct 18 '19 at 09:54

Alexander S.

1,971
2
14
22

I am getting an error: `ORA-00904: "DATEADD": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 1 Column: 30` – Oct 18 '19 at 10:01
which is the database you have to refer – Kiran Patil Oct 18 '19 at 10:08
Try to use DATEADD(DAY, -3,date_time) instead of DATEADD(DD, -3,date_time) – Alexander S. Oct 18 '19 at 10:09
@AlexanderS. same problem ... but, check my edited question please – Oct 18 '19 at 10:12
Sry, Oracle does not support DATEADD(its is MSSQL) use **to_date(date_time, 'MM/DD/YYYY')-3** like FrustratedDeveloper described. – Alexander S. Oct 18 '19 at 10:14
https://stackoverflow.com/questions/6142916/add-day-to-timestamp – Alexander S. Oct 18 '19 at 10:40

score 0 · Answer 3 · answered Oct 18 '19 at 09:55

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.

Kiran Patil · Answer 4 · 2019-10-18T11:19:51.730

0

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')

edited Oct 18 '19 at 11:19

answered Oct 18 '19 at 11:05

Kiran Patil

327
1
11

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

4 Answers4