Friday, November 4, 2011

T-SQL Combine Date with Time

I've a case where I have 2 columns, Date and Time.

Date column's datatype is DATETIME
Time Column's datatype is INT


Example:

Date:  2009-04-14 00:00:00.000
Time:  can be 743  or  0834

I need to combine them in one DATETIME column.

Select 'DTColumn' = CASE WHEN len(convert(varchar(12),Time)) = 3
     THEN cast(CONVERT(char(8),Date, 112) + ' ' + 
     convert(varchar(10),Substring(convert(varchar(12),Time, 108),1,1) + ':' + right(Time, 2) + ':00'
     ,108) AS datetime)
     WHEN len(convert(varchar(12),Time)) = 4
     THEN  cast(CONVERT(char(8),Date, 112) + ' ' + 
     convert(varchar(10),Substring(convert(varchar(12),Time),1,2) + ':' + right(Time, 2) + ':00'
     ,108)  AS datetime)
   
     End
from Table
That's it...Hope it helps someone...