Which of the following pieces of SQL is most efficient

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?

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.

StackExchange Database Administrators Q#80059, answer score: 4