Jon Winstanley Jon Winstanley - 7 months ago 13
SQL Question

How to combine date from one field with time from another field - MS SQL Server

In an extract I am dealing with, I have 2

datetime
columns. One column stores the dates and another the times as shown.

How can I query the table to combine these two fields into 1 column of type
datetime
?

Dates

2009-03-12 00:00:00.000
2009-03-26 00:00:00.000
2009-03-26 00:00:00.000


Times

1899-12-30 12:30:00.000
1899-12-30 10:00:00.000
1899-12-30 10:00:00.000

Answer

You can simply add the two.

  • if the Time part of your Date column is always zero
  • and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

It works like this due to the way the date is stored as two 4-byte `Integers` with the
left 4-bytes being the `date` and the right 4-bytes being the `time`.  

Its like doing $0001 0000 + $0000 0001 = $0001 0001

Edit regarding new SQL Server 2008 types

Date and Time are types introduced in SQL Server 2008. If you insist on adding, you can use Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to Martin Smith)

Have a look at How to combine date and time to datetime2 in SQL Server? to prevent loss of precision using SQL Server 2008 and up.