CODEWITHSUNDEEP
sqlsql-servert-sql
Karina1117
Karina1117

Reputation: 69

Combining Date and Time using DATEADD and DATEDIFF

I'm trying to create a new column by combining the date and time variables. For example, the data table looks like this and "StartDateTime" is the new variable I want to create.

  Date               StartTime                *StartDateTime*
2014-03-20    1900-01-01 10:00:00.000      2014-03-30 10:00:00.000
2015-09-23    1900-01-01 11:00:00.000      2015-09-23 11:00:00.000

I used the cast function and it seems like my current code is working.

select *,
       (cast(Date as datetime) + cast(StartTime as datetime)) as StartDateTime  
from my_table

But I just saw this line of code on a random website and it seems like it does the same thing. However, I didn't really get the logic behind it.

select *,
DATEADD(day, 0, DATEDIFF(day, 0, Date)) +   DATEADD(day, 0 -DATEDIFF(day, 0, StartTime), StartTime) As StartDateTime
from my_table

I believe the first part DATEADD(day, 0, DATEDIFF(day, 0, Date)) just returns the original date but I don't really get the second part. My understanding is DATEDIFF(day, 0, StartTime) would just return 0 and I'm not sure why 0 -DATEDIFF(day, 0, StartTime) is necessary.

Thank you.

Upvotes: 0

Views: 820

Answers (2)

HABO
HABO

Reputation: 15841

This is one way to combine the date and time values into a single DateTime2:

declare @Samples as Table ( StartDate Date, StartTime DateTime2 );
insert into @Samples ( StartDate, StartTime ) values
  ( '2014-03-20', '1900-01-01 10:00:00.000' ),
  ( '2015-09-23', '1900-01-01 11:00:00.000' );

select StartDate, StartTime,
  -- Demonstrate how to get the time with millisecond resolution from   StartTime .
  Cast( StartTime as Time(3) ) as StartTimeAsTime,
  -- Combine the time from   StartTime   with the date from   StartDate .
  --   Get the time, convert it to milliseconds after midnight, and add it to the date as a   DateTime2 .
  DateAdd( ms, DateDiff( ms, 0, Cast( StartTime as Time(3) ) ), Cast( StartDate as DateTime2 ) ) as StartDateTime
  from @Samples;

Upvotes: 1

Dale K
Dale K

Reputation: 27286

I have no idea what that code is doing. And the add operator is also not supported by all datetime datatypes. The correct solution is:

select *
  , dateadd(second, datepart(second, StartTime), dateadd(minute, datepart(minute, StartTime), dateadd(hour, datepart(hour, StartTime), [Date])))
from (values (convert(datetime2(0),'2014-03-20'), convert(time,'10:00:00.000'))) as X ([Date], StartTime);

Ideally you would store your StartTime value in a time datatype. But the above code will still work with a datetime2 datetype (which is the recommended form of datetime to use).

Upvotes: 0

Related Questions