CODEWITHSUNDEEP
sqlsql-servert-sql
Nic Mair
Nic Mair

Reputation: 1

SQL Server, swap dates in timestamp

I have a DB table that two columns of type datetime, as per below

eventTime               timeStamp               Sequence
1970-01-13 17:14:50.000 2020-03-22 18:28:15.443 5575268

What I need to do is update the year in eventTime from the year in timeStamp, but leaving the time in eventTime untouched so I end up with

eventTime               timeStamp               Sequence
2020-03-22 17:14:50.000 2020-03-22 18:28:15.443 5575268

whats the best way to do this (I have about 600k rows I need to do this to...)

Thanks.

Upvotes: 0

Views: 176

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

SQL Server allows the addition of datetime values. So:

update t
    set eventTime = convert(datetime, convert(date, timestamp)) + convert(datetime, convert(time, eventTime))
    where eventTime < convert(date, timestamp);

Here is a db<>fiddle.

You can also add the number of days:

update t2
    set eventtime = dateadd(day, datediff(day, eventtime, timestamp), eventtime)
    where eventtime < convert(date, timestamp);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

One option would be to add the difference in years between the timeStamp and the eventTime to the latter:

UPDATE yourTable
SET eventTime = DATEADD(year, DATEDIFF(year, timeStamp, eventTime), eventTime);

Upvotes: 1

Related Questions