Combine date and time in SQL Server in a SELECT query

Question

I have a From date in my table, but I want to add a correction time to it

I'm trying like this

 select cast(FromDate as date) + cast('18:00:00' as time(3)) as StartDT from WsWmpLeaveReason 

but this causes an error:

The date added operator statement is not valid for the operator add.

Answer 1

Use DATEADD :

 SELECT DATEADD(HOUR, 18, CAST(CAST(FromDate AS DATE) AS DATETIME)) as StartDT FROM WsWmpLeaveReason 

For more information, see the SQL Server DATEADD documentation for more information on DATEADD and its parameters.

Answer 2

Just like a different approach, I will close my favorite DATEADD / DATEDIFF trick DATEDIFF :

 select DATEADD(day,DATEDIFF(day,'20010101',FromDate),'2001-01-01T18:00:00') from WsWmpLeaveReason 

This works by calculating the (integer) number of days from January 1, 2001 to FromDate , and then adding the same number of (integral) days at 18:00 on January 1, 2001. This, by deduction, should produce a date that has the same date as FromDate , but with a time part fixed to 18:00.

Answer 3

I would just use datetime and add:

 select cast(cast(FromDate as date) as datetime) + cast('18:00:00' as time(3)) as StartDT from WsWmpLeaveReason; 

If FromDate already missing a time component, you can simply do:

 select cast(FromDate as datetime) + cast('18:00:00' as time(3)) as StartDT from WsWmpLeaveReason; 

You can add time to datetime , but not to date .