Converting a field from varchar to datetime

[Guest_imported]

Hi,
I am trying to convert a field from a varchar to a datetime
field: Problem is, some of the records are not of a valid date so when I try and convert I get an error. Is there a T-Sql statement that I could write that would convert the field to datetime and just make the invalid records null?
Otherwise I have to go thur all the records and correct the data. This could take days!!
thanks for your help!!
NOTE: I have always had problems w/ date fields :-(
--scott

 

[crystalized]

This should set all the non date values to null - you may want to test first on a test set of data.

UPDATE TIMETABLE
SET varcharfieldname = (CASE WHEN ISDATE(varcharfieldname) = 1 THEN varcharfieldname ELSE NULL END)

Then convert the field to a datetime. Please note the field will need to allow nulls or the statement won't work. Crystal
crystalized_s@yahoo.com

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

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[tlbroadbent]

Update the table to eliminate bad dates.

Update table
Set DateCol=Null
Where IsDate(DateCol)=0

Select statement to convert varchar to datetime, returning non-dates as null.

Select
NewDate=
Case
When isdate(DateCol)=1
Then Convert(datetime,DateCol)
Else Null
End
From Table 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.