Converting varchar to date/using isdate()

,
1

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
041129 100465
010531 122467
010429 020252
081030 062661
031231 020959
011028 091965
080928 060665

2

ISDATE is interpreting your date to be in the YYMMDD format. There is no way to tell the ISDATE function what format you are passing in. It is dependent on your language and datefirst settings.

SELECT @@language
EXEC sp_helplanguage

Since you are on SQL 2005, you cannot use TRY_PARSE or TRY_CONVERT (which are SQL 2012 functions), otherwise, those would have been the easiest way. Assuming you are on us_english, mdy then one possibility is to do the following:

ISDATE(STUFF(STUFF(DOB,5,0,'/'),3,0,'/'))

3

I haven't checked, but does ISDATE honour

SET DATEFORMAT mdy

??

4

It doesn't seem like DATEFORMAT is honored, for example:

SET LANGUAGE us_english
SET DATEFORMAT MDY
SELECT ISDATE('101098') --returns 0
SELECT ISDATE('109810') --returns 0
SELECT ISDATE('981010') --returns 1
SET DATEFORMAT YMD
SELECT ISDATE('101098') --returns 0
SELECT ISDATE('109810') --returns 0
SELECT ISDATE('981010') --returns 1
1 Like
5

Thanks for the responses. I thought it had to do with the format of the date because none of the years of the ones recognized as dates were higher than 31. For some reason at the time I was drawing a blank on how to handle it.

I ended up just writing it to another column in my staging table and then copying it to the final destination. All is good.