How to combine date and time to datetime in SQL Server?

Question

I just found the following code:

CREATE FUNCTION dbo.JoinDateAndTime
(
    @d datetime,
    @t datetime
)
RETURNS Datetime
as
    BEGIN
        declare @res datetime
        set @res = convert (datetime, Convert(varchar(10), @d, 104) + ' ' + convert(varchar(8), @t, 108), 104)
        return @res
    END
go

After seeing the answers to How to the get current date without the time part I'm sure that involving conversions to varchar can't be the best solution.

BTW I searched SO for this question, but only found a solution for mySql.

Answer 1

The time part is the fractional part and the date part the integer part, so if you date is genuinely just a date (no time part at all) and your time is genuinely just a time (no date part at all) you can simply combine them by adding them together.

For example:

SELECT CAST(40597 AS DATETIME), CAST(0.5 AS DATETIME)
SELECT CAST(40597 AS DATETIME) + CAST(0.5 AS DATETIME)

The top SELECT shows what values represent today and 12 noon, and the second line adds them to make a single value representing today at 12 noon.