Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Yes it's this old chestnut, I've looked around and haven't yet found an answer so I thought I would ask the lovely people here.In this case I have some legacy data with dates stored as strings and I want to convert them to datetime (or smalldatetime).The dates are of the form2011-07-06 11:50:45+01002011-07-06 11:51:37+01002011-07-06 14:44:22+01002011-07-06 15:47:48+01002011-07-07 14:24:30+01002011-07-07 14:37:30+0100etc.Everything I try produces the dreaded Conversion failed when converting date and/or time from character string.I'm assuming the answer is simple and that I'm just missing something crucial. Anyone have any suggestions?thankssteve-----------Insanity: doing the same thing over and over again and expecting different results.
jimf
Master Smack Fu Yak Hacker
2875 Posts
Posted - 2012-12-28 : 07:11:36
I'm not sure what the +0100 is, but if it's milliseconds this will work.DECLARE @table TABLE (DATES varchar(30))INSERT INTO @TableVALUES('2011-07-06 11:50:45+0100'),('2011-07-06 11:51:37+0100'),('2011-07-06 14:44:22+0100'),('2011-07-06 15:47:48+0100'),('2011-07-07 14:24:30+0100') select convert(datetime2(4), REPLACE(DATES,'+','.'))from @tableJimEveryday I learn something that somebody else already knew
elwoos
Master Smack Fu Yak Hacker
2052 Posts
Posted - 2012-12-28 : 07:15:36
They are the timezone data - but I think no one here will care that much about them.Thanks Jim, your solution will be sufficient to get me going.steve-----------Insanity: doing the same thing over and over again and expecting different results.
djj55
Constraint Violating Yak Guru
352 Posts
Posted - 2012-12-28 : 08:14:50
Using Jim's idea have a look at this
SELECT CONVERT(DATETIME, LEFT(DATES, 19)) AS ConvertedDateTime, DATEADD(hour, CAST(RIGHT(DATES, 4) AS NUMERIC)/100, CONVERT(DATETIME, LEFT(DATES, 19))) AS LocalDateTimeFROM @table
I am sure the divide by 100 needs tweaked, but you get the idea.djj