Convert Varchar to time

Question

Post reply

Convert Varchar to time

eseosaoregie

SSCertifiable

Points: 6889
More actions
March 11, 2010 at 8:12 am

#156770

I am currently need to format the contents of a column into a datetime. The format of the string is
Mon Jan 05 13:54:54 2009
I need to format it to 2009-01-05 13:54:54
Any help?

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

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

Hunterwood SSCrazy Points: 2074 More actions · Answer 1

declare @x varchar(40)

declare @d datetime

set @x = 'Mon Jan 05 13:54:54 2009'

set @d = substring(@x, 5, 20)

select convert(char(19), @d, 120)

/Markus

Lowell SSC Guru Points: 323518 More actions · Answer 2

==edit== nice job hunterwood! you posted right when i did, yours is more elegant.==end edit==

yeah non-standard formats are going to be tough to convert. you have to substring the parts together.

here's how i would do it:

--results

Reformated NowADate NewFormat

-------------------- ----------------------- ------------------------------

Jan 05 2009 13:54:54 2009-01-05 13:54:54.000 2009-01-05 13:54:54

the code i used:

--trying to get formatted like Mar 11 2010 10:16:36:483AM --"109" date format

declare @uglydate varchar(50)

set @uglydate = 'Mon Jan 05 13:54:54 2009'

select

--in the format needed to convert

substring(@uglydate,5,7)+ right(@uglydate,4) + substring(@uglydate,11,9) As Reformated,

--now it's a date

convert(datetime,substring(@uglydate,5,7)+ right(@uglydate,4) + substring(@uglydate,11,9),109) as NowADate,

--now convert yet again to the desired varchar~datetime format.

convert(varchar,

convert(datetime,substring(@uglydate,5,7)+ right(@uglydate,4) + substring(@uglydate,11,9),109),

120) As NewFormat

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

Thanks that seems to work. I tried something like that initially but kept on getting errors when parsing it.

Cheers

Paul White SSC Guru Points: 150467 More actions · Answer 4

eseosaoregie (3/11/2010)
I am currently need to format the contents of a column into a datetime. The format of the string is
Mon Jan 05 13:54:54 2009
I need to format it to 2009-01-05 13:54:54
Any help?

Do not store dates and times as strings, in any format.

Store them as one of the SQL Server date/time types and format at the presentation layer.

SELECT CONVERT(DATETIME, RIGHT('Mon Jan 05 13:54:54 2009', 20), 0);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi