How do I combine data from two DateTime columns (SQL Server)?

Using SQL Server 2005, I am working with a database which has two columns to keep track of what is essentially a single DateTime (and I can’t for the life of my figure out why anyone would ever think this is a good idea).

Basically, one column, let’s call it OrderDate, contains a DateTime like this: 2008-02-29 00:00:00.000, and the other column, OrderTime, has one like this: 1900-01-01 11:15:00.000

  • Where should the partitioning column go in the primary key on SQL Server?
  • Audit logout - high CPU Utilization
  • mySQL for SqlBulkCopy
  • How to compare ip addresses in SQL Server?
  • substr() expects parameter 1 to be string (SQL SERVER PHP 5 to PHP 7)
  • SqlBulkCopy ColumnMapping error CSV to DataTable to SQL Server Table
  • So, now I am forced to combine these two whenever I want to display the entire date and time in one place. This is easy from a UI perspective, but how would I do this at the DB level? I want to be able to write a query finds a DATEDIFF between the combined date and time from these two columns and compares it to another DateTime value.

    Ex:

    SELECT DATEDIFF(mi, CompletionTime, OrderTime) AS TurnAroundTime FROM SomeRecord
    

    The problem with the above being of course that OrderTime’s date component is 1900-01-01, resulting in a slightly skewed result…

    2 Solutions collect form web for “How do I combine data from two DateTime columns (SQL Server)?”

    Use “OrderDate + OrderTime” in SQL to create “OrderDateTime”. Or define a computed column.

    The SQL standard supports standard addition on datetimes, I’m sure, and SQL Server implements it.

    There is an example in BOL

    Create a computed column that adds them together

      Alter Table TableName Add CompleteDate as DateOnlyColName + TimeOnlyColName
    

    to make sure that errant time data in the DateOnlyColName is not inadvertenetly added in, use this instead…

      Alter Table TableName Add CompleteDate as 
             DateAdd(day, DateDiff(day, 0, DateOnlyColName), 0) + 
                          TimeOnlyColName
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.