Convert Varchar to DataTime 1

[dhoward007]

I have a column in my db that stores the date value as the following in varchar

07292002163002
mmddyyyyhhmmss

I need to convert this to datetime and am having no luck.

below is my attempt.

select convert(datetime, timestampd, 109) from stage_uptime

Any suggestions??

 

[tlbroadbent]

Style 109 is mon dd yyyy hh:mi:ss:mmmAM (or PM). Your statement fails because the format doesn't match the style you specified. You have to convert the string to a format that SQL accepts as a datetime format or style in order to convert it. See the table of styles under the topic "CAST and CONVERT" in SQL BOL.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Here is an example of converting the string to style 120 using the Stuff function. You can use any method you prefer to coonvert the string. The statement then converts the resulting style 120 date string to datetime.

Select
convert(datetime,
Stuff(Stuff(Stuff(Stuff(Stuff(timestampd,
13,0,':'),11,0,':'),9,0,' '),
5,0,'-'),3,0,'-'), 120)
From stage_uptime Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[dhoward007]

Thanks tlbroadbent.. This worked great. I have never used or seen stuf. This is awesome..