user2430797 user2430797 - 4 months ago 10
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

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
('2016-05-09T00:00:00.000','1899-12-30T12:26:00.000')

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

Result:

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