Adding a date and the time portion of a datetime2 together

Question

I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2. I want to combine load_date with the time portion of load_time and create a datetime2.

It's in an existing table, and I want to SET the load_time to this new value. Below is a SELECT that appears to work, so I can turn this into an UPDATE statement. However, I wanted to know if there is a more elegant or less verbose way.

SELECT
    [load_date]
    , [load_time]
    , DATEADD(DAY, DATEDIFF(DAY, CAST(CAST([load_time] AS TIME) AS DATETIME2), CAST([load_date] AS DATETIME2)), CAST(CAST([load_time] AS TIME) AS DATETIME2))

Answer 1

You can use CONCAT and rely on implicit conversion to do it less verbosely. Both queries use 0 CPU, so you may have to run a lot of iterations to see if one is significantly more efficient than the other.

SELECT 
    [load_date], 
    [load_time], 
    CONCAT([load_date], ' ', CAST([load_time] AS TIME)) 
FROM [test_table_1]

You aren't required to explicitly convert it to DATETIME2 because it will be converted implicitly when it updates the load_time column. Note that having the shortest code is not always the best. Using comments to explain what the code is doing will prevent anyone from having to figure out what it's doing.