mardi 12 janvier 2016

converting varchar to date/Using isdate()

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
41129   100465
10531   122467
10429   20252
81030   62661
31231   20959
11028   91965
80928   60665

Aucun commentaire:

Enregistrer un commentaire