I have a flat file that I am importing into a SQL Server 2005 staging table as character data.
I need to convert the birthday field to datetime format when copying it to the final destination table. I was doing so using the following:
BIRTHDAY = case when isdate(DOB)=1 then convert(datetime, '19'+right(DOB, 2)+left(DOB, 2)+substring(DOB,3,2)) else null end
The problem is only 100+ of the birthdays from the 32k+ file are identified as dates.
I cannot see a difference between the ones that are dates and the ones that aren't. I have included a sampling below.
good date bad date