Add time to a date

Question

I need to create a smalldatetime from a smalldatetime with no time and add the time from a GETDATE() value.  For example, if I have a smalldatetime value of 2018-12-05 00:00:00 and I want to add/replace the time of 00:00:00 with the hr:min:sec from the current value of GETDATE(), how do I do that?

Answer 1

You could simply try this:

SELECT
DATEADD(day,DATEDIFF(day,getdate(),smalldatetimecolumn)
    ,getdate())

---

Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

declare @dt smalldatetime=' 2018-12-05 00:00:00'

Select Cast(@dt as smalldatetime) + Cast(Cast(getdate() as time) as smalldatetime)