Convert string to datetime

Question

Post reply

Convert string to datetime

sql_novice_2007

Hall of Fame

Points: 3307
More actions
July 14, 2011 at 4:41 pm

#253770

How can I convert the following string to date time data.
July 7 2011 12:47 pm EDT
I need to convert this in a table with 1000000 rows.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Provided that EDT is NOT required this is one way, and for a million rows it will NOT be quick.

DECLARE @T VARCHAR(20)

SET @T = 'July 7 2011 12:47 pm EDT'

SET @T = REPLACE(@T,'EDT','')

SELECT

@T AS UnconvertedText,

CAST(@T AS datetime) AS UsingCast,

CONVERT(datetime, @T, 126) AS UsingConvertFrom_ISO8601 ;

Results:

UnconvertedText UsingCast UsingConvertFrom_ISO8601

July 7 2011 12:47 pm 2011-07-07 12:47:00.000 2011-07-07 12:47:00.000

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

Actually, taking BitBucket's lead, the following is quite quick... provided you want (and you should want) a real DATETIME datatype.

SELECT CAST(REPLACE(SomeVarcharDateColumn,' EDT','') AS DATETIME)

FROM dbo.MillionRowTable

The real question would be, will you only have "EDT" or will you also "CST", "EDST", "PST", etc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 3

I have other time zones as well. But based on your help I was able to convert all the other data.

Thanks.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

sql_novice_2007 (7/15/2011)
I have other time zones as well. But based on your help I was able to convert all the other data.
Thanks.

Very cool. Would you post your solution so that it can help others who may have the same question? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 5

sql_novice_2007 (7/14/2011)
How can I convert the following string to date time data.
July 7 2011 12:47 pm EDT

The conversion is the easy part. Don't forget to think about how your users will want to manipulate the data. You might choose to convert the data to UTC and preserve the time zone information in a separate field, or you might like to take a look at the new DATETIMEOFFSET data type in SQL Server 2008.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi