Pfthb

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:

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

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

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

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

( here you put where clause for you required)