CODEWITHSUNDEEP
sqlsql-serverazure-sql-database
Joe Ruder
Joe Ruder

Reputation: 2194

change just the date portion of a sql record

Using SQL (Azure Sql) I have a number of records that contain datetime values such as: 3/16/19 9:25 AM 3/16/19 10:15 AM

I need these to all be todays date but retain their time portion.

I am a C# programmer so my SQL is a bit rusty.

So pseudo code would be something like (asuming requested_delivery_date is the field I need updated.

update set requested_delivery_date = requested_delivery_date.dateadd(7 days)

so anything after this that had a date of 3/20/19 9:30 am would now be 3/27/19 9:30 am

Upvotes: 1

Views: 44

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82504

Use a combination of DateAdd and DateDiff:

DECLARE @Date As datetime = '2019-03-01T15:32:44'

SELECT  @Date As Source,
        GETDATE() As Today,
        DATEADD(DAY, DATEDIFF(DAY, @Date, GETDATE()), @Date) As Result

Result:

Source                  Today                   Result
2019-03-01 15:32:44     2019-03-28 08:27:29     2019-03-28 15:32:44

To update the column in the table you simply need to write an update statement:

update TableName
set requested_delivery_date = DATEADD(DAY, DATEDIFF(DAY, requested_delivery_date , GETDATE()), requested_delivery_date)
-- add a where clause to only update some of the records

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16431

I tried this and successfully. This is my table.

startTime   endTime
2019-03-28 15:55:10.690 2019-01-01 09:00:00.000

My SQL statement:

UPDATE [MyDatabase].[dbo].[deom1] SET [endTime]=DATEADD(DAY, DATEDIFF(DAY, [endTime], GETDATE()), [endTime])

And this the result:

startTime   endTime
2019-03-28 15:55:10.690 2019-03-28 09:00:00.000

For more details, please see DATEADD (Transact-SQL).

Hope this helps.

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

It looks like a simple UPDATE statement that updates all rows of YourTable.

UPDATE YourTable
SET requested_delivery_date = 
    DATEADD(DAY, DATEDIFF(DAY, requested_delivery_date, GETDATE()), requested_delivery_date)
;

If you want to add a constant number of days, then the query is even simpler:

UPDATE YourTable
SET requested_delivery_date = 
    DATEADD(DAY, 7, requested_delivery_date)
;

Upvotes: 1

Related Questions