Maria
Maria

Reputation: 760

Update all date columns in SQL Server -1 day

I want to update my database (SQL Server Express) all the dates for specific ids.

I am displaying the ids I want to.

SELECT TOP (1000) ID, Dates
FROM tbl_table
WHERE (id IN (29695, 29700, 29701, 29702, 29703, 29704, 29705, 29706, 29707, 29708, 29709, 29710, 29711, 29712, 29713, 29714, 29715))

AND my dates in the database are like this:

enter image description here

Is there any way to update all the date columns with same date - 1 day?

For example: if we have 2019-12-20, update it to 2019-12-19?

For example if I want to do it in PHP, I would loop through this query and fetch all all dates. After I would remove one day like this:

date('m/d/Y', strtotime($date. ' - 1 days');

And create a query to update all the columns based on id. I just want to avoid that. Is there any SQL command that do that?

Thanks

Upvotes: 4

Views: 13124

Answers (3)

Kobi
Kobi

Reputation: 2524

The request below will update the rows you want by adding -1 days on each date:

UPDATE tbl_table 
SET    dates = Dateadd(day, -1, dates) 
WHERE  id IN ( 29695, 29700, 29701, 29702, 
               29703, 29704, 29705, 29706, 
               29707, 29708, 29709, 29710, 
               29711, 29712, 29713, 29714, 29715 )

DATEADD function takes 3 parameters:

  1. the interval ( day, month, year ..)
  2. the increment (the value to add or remove if negative)
  3. an expression (wich is a datetime type)

See DATEADD documentation

Upvotes: 6

zulqarnain
zulqarnain

Reputation: 1

UPDATE tableName SET date= DATEADD(d,-1, date)  
where ....

( here you put where clause for you required)

Upvotes: 0

Ryan
Ryan

Reputation: 8005

To return a query with the previous day:

SELECT TOP (1000) ID, Dates, DATEADD(dd, -1, Dates) AS PreviousDay
FROM tbl_table

To update the table with the previous day:

UPDATE tbl_table
SET    Dates = DATEADD(dd, -1, Dates)
FROM   -- Put your conditions here

Upvotes: 3

Related Questions