Combine date and time in SQL Server in SELECT query

Question

I have From date in my table but I want to add fix 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 is causing an error:

Operand data type date is invalid for add operator.

Answer 1

Use DATEADD:

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

See the freely available, comprehensive SQL Server Books Online documentation for more details on DATEADD and its options

Answer 2

Just as a different take on things, I'll wheel out my favourite DATEADD/DATEDIFF trick once again:

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

This works by computing the (integral) number of days since 1st January 2001 to FromDate, and then adding that same number of (integral) days onto 18:00 on 1st January 2001. This, by deduction, must produce a date that has the same date as FromDate, but with the time portion fixed to 18:00.

Answer 3

I would just use datetime and addition:

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

If FromDate is already lacking a time component, you can just do:

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

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