CODEWITHSUNDEEP
sql-serverdatetimeconcatenation
Raggedtoad
Raggedtoad

Reputation: 521

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.

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...

Upvotes: 1

Views: 4541

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146409

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

Upvotes: 2

gbn
gbn

Reputation: 432180

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

Upvotes: 2

Related Questions