asked
CONVERTING DATE TIME INTEGER TO DATETIME
Hi, I have two cloumns 1)date 2)time. In date column the value is stored as '20120125' In time column the value is stored as '070550' What I need is, to combine these column values and insert into one target datetime column named used_date. Means It should be in the following format '2012-01-25 07:05:50.000' ----->used_date How to do this?? Thanks in Advance.
One possible solution: DECLARE @dateTable TABLE ( DateCol int, TimeCol int ) INSERT INTO @dateTable (DateCol, TimeCol) VALUES (20120125, 070550) SELECT CONVERT(datetime, CONVERT(char(8), DateCol), 112) + CONVERT(datetime, STUFF(STUFF(RIGHT('0' + CONVERT(varchar(6), TimeCol), 6), 5, 0, ':'), 3, 0, ':')) FROM @dateTable If the source columns are not of type integer but any character type, then you do not need the initial cast of the integers to characters and the final select will look like SELECT CONVERT(datetime, DateCol, 112) + CONVERT(datetime, STUFF(STUFF(RIGHT('0' + TimeCol, 6), 5, 0, ':'), 3, 0, ':')) FROM @dateTable
