user2430797 user2430797 - 1 year ago 99
SQL Question

SQL Server - add DATE part of DATETIME to TIME part of DATETIME

In SQL Server 2005 I am interrogating some old legacy data and I need to combine the date component of a datetime column with the time component from another column. Here's an example:

DateColumn: 2016-05-09 00:00:00.000
TimeColumn: 1899-12-30 12:26:00.000

I need the end result converted to the following DateTime:

ResultDateTime: 2016-05-09 12:26:00.000

I tried using:

CAST(DateColumn AS DATETIME) + CAST(TimeColumn AS TIME) AS ResultDateTime

But SQL Server 2005 doesn't recognize the type TIME.

Can someone please show me a way of doing that?

Many thanks!

Answer Source

You can just use DATEADD and DATEDIFF, assuming that the time column's date portion is always 30/12/1899:

declare @t table (DateColumn datetime,TimeColumn datetime)
insert into @t(DateColumn,TimeColumn) values

select DATEADD(millisecond,DATEDIFF(millisecond,'18991230',TimeColumn),DateColumn)
from @t


2016-05-09 12:26:00.000
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download