Tuesday, February 7, 2012

T-Sql Combine date and 6 digits time

A recent challenge about how to combine 2 columns; The date column has 2011-08-24 00:00:00.000 and the Time column has 120622


DECLARE @D datetime, @T INT
select @D ='2011-08-24 00:00:00.000 ', @t = 120622

SELECT @D + CAST(DATEADD(SECOND, @T %100 + (60*( @T %10000 / 100)) + 3600*(@T /10000),0) AS time)


And that gives you:

2011-08-24 12:06:22.000