CODEWITHSUNDEEP
t-sqldatetimedateadd
Raffaeu
Raffaeu

Reputation: 6973

T-SQL change Time Portion of a DateTime, using another DateTime Time portion

I have a select statement that returns the following:

SELECT
   StartDate
   ,EndDate
FROM
   MyTable

And the result returns the following data:

enter image description here

What I want is change the time portion of StartDate using the value from EndDate. So that only the time portion of both dates is the same, taken from EndDate. I have to keep identical, down to milliseconds.

Is it possible using DATEADD function?

Upvotes: 0

Views: 27

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Using DateAdd and DateDiff:

SELECT DATEADD(DAY, DATEDIFF(DAY, EndDate, startDate), EndDate) As StartDate,
       EndDate
FROM MyTable

I'm adding the days difference between the start date and the end date. Since the DateDiff have the later date before the earlier date, it returns the days difference as a negative number.

Another option that I'm guessing some people will find more readable is this:

SELECT DATEADD(DAY, -DATEDIFF(DAY, startDate, EndDate), EndDate) As StartDate,
       EndDate
FROM MyTable

Upvotes: 1

Related Questions