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
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) + TimeOnlyColName