Thursday 28 August 2008

Get Date from UTC (Coordinated Universal Time) String

Get Date from UTC (Coordinated Universal Time) String

DECLARE @INPUTDATE VARCHAR(12),
@OUTPUTDATE VARCHAR(12)

SET @INPUTDATE = '2006-7-29T13:55:6+0:0'

-- NEGATE TIME >
SELECT CHARINDEX('T',@INPUTDATE)
SELECT @INPUTDATE = LEFT(@INPUTDATE,CHARINDEX('T',@INPUTDATE)-1)
SELECT @INPUTDATE

-- SEPARATE OUT YEAR, MONTH, DAY, PAD SINGLE DIGIT VALUES WITH ZEROES AND ADD BACK INTO SINGLE STRING WHICH CAN BE CONVERTED TO DATETIME
SELECT @OUTPUTDATE = LEFT(@INPUTDATE,4) + RIGHT('0' + REPLACE(SUBSTRING(@INPUTDATE,6,2),'-',''),2)+ RIGHT(REPLACE('0' + SUBSTRING(@INPUTDATE,8,3),'-',''),2)
SELECT @OUTPUTDATE

-- CONVERT TO DATETIME
SELECT CONVERT(DATETIME,@OUTPUTDATE,112)

No comments: