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.
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
DATEADD: "The return data type is the data type of the date argument, except for string literals")
DATE, then it doesn't allow housr...... so you need to cast to DATE first to get zero time, then back to DATETIME so you can add hours..... thanks for pointing this out!
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.
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.
