SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Converting strings to dates!!		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2040 Posts
Posted - 12/28/2012 :  04:47:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 12/28/2012 :  07:11:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2040 Posts
Posted - 12/28/2012 :  07:15:36  Show Profile  Reply with Quote
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
Posting Yak Master

USA
100 Posts
Posted - 12/28/2012 :  08:14:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000