Posted By: Jon
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
2009-03-12 00:00:00.000 2009-03-26 00:00:00.000 2009-03-26 00:00:00.000
1899-12-30 12:30:00.000 1899-12-30 10:00:00.000 1899-12-30 10:00:00.000
You can simply add the two.
- if the
Time partof your
Datecolumn is always zero
- and the
Date partof your
Timecolumn 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
Integerswith the left 4-bytes being the
dateand the right
4-bytes being the
time. Its like doing
$0001 0000 + $0000 0001 =
Edit regarding new SQL Server 2008 types
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.