CODEWITHSUNDEEP
sqlsql-server
simon831
simon831

Reputation: 5476

Update a DateTimes's Time, but not the date

MyTable:
 id     int
 Date1  DateTime
 Date2  DateTime

How can I set the time of Date2 to be the same as Date1 without affecting the date of Date2?

Upvotes: 1

Views: 101

Answers (3)

tobias86
tobias86

Reputation: 5029

Update Date2 with only the difference in days between the two dates.

UPDATE MyTable
SET Date2 = DATEADD(DAY, DATEDIFF(DAY, Date1, Date2), Date1);

This will preserve the date portion of Date2.

UPDATE: This method essentially reconstructs Date2 by using Date1 as a reference and only adding the difference in days between Date1 and Date2 - preserving the TimeStamp from Date1.

Upvotes: 3

t-clausen.dk
t-clausen.dk

Reputation: 44316

This works from sql server 2008+

select cast(cast(date2 as date) as datetime) + cast(date1 as time) newdate2
from (select getdate() date2, cast('2012-01-01 20:00' as datetime) date1) a

Upvotes: 1

PaulStock
PaulStock

Reputation: 11263

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @Date2)) + DATEADD(day, -datediff(day, 0, @Date1), @Date1)

Upvotes: 0

Related Questions