SQL Combine Date and Time columns into single DateTime column

Question

I would like to combine two datetime2 columns (called Date and Time respectively) into a single datetime2 column in the same table. The 2 old columns will be removed afterwards.

This query works when testing the combine.

SELECT CAST(DATEADD(day, 0, DATEDIFF(day, 0, Date)) AS DATETIME)
    + CAST(DATEADD(day, 0 - DATEDIFF(day, 0, Time), Time) AS DATETIME)
    FROM dbo.Orders

Currently I've tried the following, but I can't figure out how to put the results into an existing column called OrderDate.

SOLVED

UPDATE dbo.Orders 
SET
OrderDate = CAST(DATEADD(day, 0, DATEDIFF(day, 0, Date)) AS DATETIME)
+ CAST(DATEADD(day, 0 - DATEDIFF(day, 0, Time), Time) AS DATETIME);

Answer 1

Unless all the times in the time column end with milliseconds that have a 0, 3, or 7 in the units position (I would never bank on such a thing), there will be rounding errors when you convert the time to DATETIME.

Here are two of the more extreme examples of what can happen when the units position of time is a "9" during conversion from DATETIME2 to DATETIME.

--========================================================================================
--      Demonstrate the erroneous results of using a conversion to DATETIME.
--========================================================================================
   DROP TABLE IF EXISTS #TestTable; --Just to make reruns in SSMS easier.
GO
--===== Create and populate the test table on-the-fly.
 SELECT  v1.RowNum 
        ,Date = CONVERT(DATETIME2,v1.Date)
        ,Time = CONVERT(DATETIME2,v1.Time)
        ,v1.Expected
   INTO #TestTable
   FROM (VALUES
         (1,'2022-12-21','22:59:59.999','2022-12-21 22:59:59.999')
        ,(2,'2022-12-31','23:59:59.999','2022-12-31 23:59:59.999')
        )v1(RowNum,Date,Time,Expected)
;
--===== Show that the currently accepted answer (posted on Mar 2, 2022) is INCORRECT.
   WITH cteCombine AS
(
 SELECT  *
        ,Combined = cast(dateadd(DAY, 0, datediff(DAY, 0, date)) AS datetime)
                        + cast(dateadd(DAY, 0 - datediff(DAY, 0, time), time) AS datetime)
   FROM #TestTable
)
 SELECT  *
        ,Result = IIF(CONVERT(DATETIME2,Combined) = CONVERT(DATETIME2,Expected)
                     ,'Correct','Incorrect')
   FROM cteCombine
  ORDER BY RowNum
;

Here are the results from the test code above. The "Combined" column doesn't match the "Expected" column.

Here's one way to overcome the sometimes extreme rounding issues of DATETIME using the same data as the test code above.

--========================================================================================
--      Demonstrate the results of a correct method to avoid the rounding of DATETIME.
--========================================================================================
   DROP TABLE IF EXISTS #TestTable; --Just to make reruns in SSMS easier.
GO
--===== Create and populate the test table on-the-fly.
 SELECT  v1.RowNum 
        ,Date = CONVERT(DATETIME2,v1.Date)
        ,Time = CONVERT(DATETIME2,v1.Time)
        ,v1.Expected
   INTO #TestTable
   FROM (VALUES
         (1,'2022-12-21','22:59:59.999','2022-12-21 22:59:59.999')
        ,(2,'2022-12-31','23:59:59.999','2022-12-31 23:59:59.999')
        )v1(RowNum,Date,Time,Expected)
;
--===== Show Correct Method.
   WITH cteCombine AS
(
 SELECT  *
        ,Combined = CONVERT(DATETIME2
                    ,CONVERT(VARBINARY(6),CONVERT(TIME,Time))
                    +CONVERT(BINARY(3),CONVERT(DATE,Date)))
   FROM #TestTable
)
 SELECT  *
        ,Result = IIF(CONVERT(DATETIME2,Combined) = CONVERT(DATETIME2,Expected)
                     ,'Correct','Incorrect')
   FROM cteCombine
  ORDER BY RowNum
;

And here are the results from that bit of code. The "Combined" column matches the "Expected Column"

Answer 2

Why the subquery? Just use the expression directly. (Just for the record: Maybe the expression can be optimized/simplyfied as well, I didn't look into this.)

UPDATE dbo.orders 
       SET orderdate = cast(dateadd(DAY, 0, datediff(DAY, 0, date)) AS datetime)
                       + cast(dateadd(DAY, 0 - datediff(DAY, 0, time), time) AS datetime);