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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Converting strings to dates!!

Author  Topic 

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2012-12-28 : 04:47:29
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 form

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
2011-07-07 14:37:30+0100

etc.

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?

thanks

steve

-----------

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 @Table
VALUES
('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 @table

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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.
Go to Top of Page

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 LocalDateTime
FROM @table

I am sure the divide by 100 needs tweaked, but you get the idea.

djj
Go to Top of Page
   

- Advertisement -