converting nvarchar to datetime field

[zerodot]

I have been given the task of selecting from a small database (28,000 records) certain records that indicate a date over under 6 years old. The only problem is that the DBA didn't make the column a datetime field when he designed the database, he made an nvarchar. So when I'm trying to use the DateAdd function, it will convert some fine, but others it will error out on. for instance it can convert 1/1/2002 to datetime just fine, but not "Sept. 10 1999". AND furthermore there are about 1000 null values for the date (when the indicated field was irrelevant to the record). Also note that by date I don't mean the time of record creation, it's an arbitrary field indicating a date related to the record.

Is there any way possible to convert such a row to datetime? it would need to convert all the character fields along with the nulls, but I don't think those will matter. Is there a 3rd party program? there has to be some way to do it.

thanks in advance.

 

[mwolf00]

If you could write an asp page, you could use the cDate() function in vbScript to convert 1/1/2002 as well as Sept. 10 1999. It would have no problem recognizing any valid date format....

do while not objRS.EOF
convertedDate = objRS("textDate&quot
if isDate(convertedDate) then
sql = "Update myTable Set dateField = '"& cDate(convertedDate) &"' WHERE pkID = " & objrs("pkid&quot
objCN.execute(strsql)
end if
objrs.movenext
loop Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com

 

[JamesLean]

Create a new column on the table of type datetime. Then run an update query to fill out those values by converting the textual dates, eg:

UPDATE table1
SET NewDateCol = CONVERT(datetime, OldTextCol)
WHERE OldTextCol IS NOT NULL

Having said this, values like 'Sept. 10 1999' are going to cause you problems with the convert (it's not going to work!) so you will have to do some cleaning up maybe?

 

[tlbroadbent]

You can identify rows conaining columns that contain valid dates by using the IsDate function in SQL.

UPDATE table1
SET NewDateCol = CONVERT(datetime, OldTextCol)
WHERE OldTextCol IS NOT NULL
AND IsDate(OldTextCol)=1

Then list the non date columns.

Select * From Table1
Where OldTextCol Is Not Null
And IsDate(OldTextCol)=0 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[zerodot]

thanks for the tips guys, I used the ASP page suggestion and it worked well for the most part. so now everything is in the MM/DD/YYYY format. but now for some reason it won't let me conver the column into datetime giving me an "arithmetic overflow error converting to data type datetime.

any ideas on why this is? there aren't any more nulls and everything is in the above format.

 

[mwolf00]

My asp solution was designed to insert the data into a datetime field so no conversion was needed afterwards... How are you trying to convert it now? Get the Best Answers! faq333-2924
Happy 2003!
mikewolf@tst-us.com