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

  • How to make a log as small as possible in a SQL database?
  • Is it better to execute many sql commands with one connection, or reconnect every time?
  • Stored Procedure providing inconsistent return values
  • SQL Server agent job account issue
  • Commas within CSV Data
  • Designing a conditional database relationship in SQL Server
  • 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.