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.


    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) + 
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.