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

If the time element of your date column and the date element of your time column are both zero then Lieven's answer is what you need. If you can't guarantee that will always be the case then it becomes slightly more complicated:

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) +
    DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column)
FROM your_table

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.

This is an alternative solution without any char conversions:

DATEADD(ms, DATEDIFF(ms, '00:00:00', [Time]), CONVERT(DATETIME, [Date]))

You will only get milliseconds accuracy this way, but that would normally be OK. I have tested this in SQL Server 2008.