Converting date stored as a VARCHAR to a datetime 1

[pabowen]

I am working with a database where the date was stored as a varchar (25) in the following format: 11/8/2002

I need to convert this to a type datetime, if possible without creating another column or table. I am working with a several million row database and need a quick (or at least quick as possible) way to convert this.

Thanks for the help.

 

[tlbroadbent]

If all rows contain valid datetime values or nulls you can run an Alter Table statement to convert the column to datetime.

ALTER Table YourTable Alter Column YourDateCol datetime

You may want to test the column for invalid dates and replace those values with null or a valid date. Then you can Alter the table schema.

Update YoutTable
Set YourDateCol=null
Where IsDate(YourDateCol)=0 Terry L. Broadbent - DBA
Computing Links:

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

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

 

[pabowen]

Tried that but I am receiving 'syntax error converting a datetime from a character string'

This might be because the data is actually stored in this format 12/25/2002 not the decimal format that it might be expecting i.e. 12252002

 

[dbldba]

Are you wanting to actually the data format in the table or are you writing a query / stored proc to manipulate the data?

 

[MeanGreen]

Before you try to convert the data, run this query to find those rows that will not convert. You may have to cleanup your data before you can convert the column:

select * from yourtable
where isdate(columntoconvert) = 0

Hope this helps.

 

[tlbroadbent]

Is 'syntax error converting a datetime from a character string' the actual error message?

The format 12/25/2002 will work. 12252002 wil not work. How is the date actually formatted in the column.
Terry L. Broadbent - DBA
Computing Links:

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

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

 

[pabowen]

It appears not all the dates were in date time format.
The statement

select * from yourtable
where isdate(columntoconvert) = 0

showed me several thousand rows where the date was 12/26/0220 or 1252002. So I updated that and am converting the table now. 2.7 million rows will take a little while.

Thank you everyone, especially tlbroadbent and meangreen!