varchar to datetime SQL Server 2000

WrathOfAchilles · 25 Mar 2008 at 14:10

Hi all,

I have a varchar field that contains dates in the format yyyy-mm-dd and i want to convert this field to a date field.

The thing is, the data was origionally supplied as an xml file and the bulk loader doesn't support datetime types which is why it was loaded as a varchar.

I guess its a convert statement but i'm struggling to put one together. Any help would be great.

Cheers,
Martin

WrathOfAchilles · 25 Mar 2008 at 15:18

Ok, an update,

What my research has found (if i understand it correctly of course):

The date data i have in the varchar field doesn't meet any of the Sql Server style types (eg dd/mm/yy etc). The closest one to the yyyy-mm-dd i have would be yyyy.mm.dd which is ANSI Style type (code 102).

In order for me to convert the varchar field, i will have to write a replace statement to replace all the - with . and then write a cast or convert statement to change the field to datetime.

This prob isn't what i'm going to want to do though as ill be changing the format of the data.

Cheers,
Martin

GravyMonster · 25 Mar 2008 at 15:33

What exactly is it you now need to do?

Looks like you've already answered your OP with the CAST(REPLACE(DateField)) idea

WrathOfAchilles · 25 Mar 2008 at 16:04

Hmm, think i have confused myself now

assume table name is temp_1 and the varchar field that contains the dates in the format yyyy-mm-dd is called m_date, how would the cast statement look?

Cheers!
Martin

WrathOfAchilles · 25 Mar 2008 at 16:41

Turns out i been talking rubbish lol.

Should this thread ever be found by someone else with similar problem, see below code:

select convert(datetime,pad_date), pad_date from xml_customeraudit

Turned out the format yyyy-mm-dd is perfectly acceptable. My ramblings above i "think" were for converting the other way (datetime to varchar) where i want varchar to datetime.

Anyway, im done

Can close if you want.

Cheers,
Martin