Which of the following pieces of SQL is most efficient

Question

I need to add the date part of GetDate() to the time part of a stored DateTime.

Naively I thought I could do the following:

declare @testTime DateTime = '2013-04-23 13:55:06'
select cast(getdate() as date) + cast(@testTime as time)

but this gives the following error:

Operand data type date is invalid for add operator.

I did some more research and have come up with three possible ways of doing the calculation:

declare @testTime DateTime = '2013-04-23 13:55:06'

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

All three produce the same result but I'm not 100% happy about implementing any of them, though the third way seems to be a better way - though I have no evidence for this.

Which (if any) of the three is the most efficient?

Is there a better way?

Answer 1

You could use DATETIMEFROMPARTS (Transact-SQL)

select datetimefromparts(
                        datepart(year, getdate()), 
                        datepart(month, getdate()), 
                        datepart(day, getdate()), 
                        datepart(hour, @testTime), 
                        datepart(minute, @testTime), 
                        datepart(second, @testTime),
                        datepart(millisecond, @testTime)
                        )

Update:

The first two of your queries.

select dateadd(dd, 0, DATEDIFF(dd, 0, GetDate())) + cast(@testTime as time)

select cast(cast(GetDate() as date) as datetime) + cast(@testTime as time)

require that the database compatibility level is SQL Server 2008(100). If they are executed in compatibility level (110) or (120) you will get

Msg 402, Level 16, State 1, Line 3 The data types datetime and time are incompatible in the add operator.

so you should not consider those at all.

The last query

select dateadd(day, datediff(day, @testTime, GetDate()), @testTime)

works just fine.

I did a test over 1000000 rows in SQL Server 2014 and the datetimefromparts version took 650 ms and dateadd/datediff took 350 ms.

Answer 2

Tested on SQL 2005 only:

declare @testTime DateTime 
set @testTime = '2013-04-23 13:55:06'
select cast(substring(cast(getdate()as binary(8)),1,4)
            + cast(@testTime as binary(4)) 
            as datetime)

Why, how?

-- These two have the same last 8 hexadecimal digits,
-- and vary one digit before that
-- This shows that the date and the time 
-- are physically separate 32-bit numbers
select cast(getdate()   as binary(8)) union all 
select cast(getdate()+1 as binary(8)) 
-- last 32-bits: get time
select cast(getdate()   as binary(4)) 
-- first 32-bits: get date
select substring(cast(getdate()   as binary(8)),1,4) 

Some will argue that I should not be relying on undocumented internals of data-structures in code. That may be true. But if for some reason performance matters enough, I think CAST should be quicker. I haven't done any timings.