When IsDate Does Not Work

IsDate is an often used feature used in TSQL to determine whether or not a field contains a date. I am sure there are a number of people who have used this successfully and may wonder what I could possibly be talking about. To maintain my technical bona fides I’ve included some code, which you can run inside of SQL Server Management Studio.

 Declare @ValidDateTest as table ( Somefield varchar(15), Works bit)
 Insert into @validDateTest values ('Not a date' , 0)
 Insert into @validDateTest values ('4-1-2-14' , 0)
 Insert into @validDateTest values ('5-2-7' , 0)
 Insert into @validDateTest values ('2014.2.3' , 1)
 Insert into @validDateTest values ('08/02/10' , 0)
 Insert into @validDateTest values ('7/3/2015' , 1)
 Insert into @validDateTest values ('2014-3-14' , 1)
 Insert into @validDateTest values ('12-3-1' , 0)
 Insert into @validDateTest values ('14-3-4' , 0)
 Insert into @validDateTest values ('20140301' , 1)
 Insert into @validDateTest values ('201123' , 1)
 Insert into @validDateTest values ('2011204' , 0)
 Insert into @validDateTest values ('7/023/2015' , 0)
 Insert into @validDateTest values ('6/02/014' , 0)
 Insert into @validDateTest values ('003/02/014' , 0)
 Insert into @validDateTest values ('3/010/2014' , 0)
 Insert into @validDateTest values ('4/02/012' , 0)
Select case when isdate(somefield) = 0 then --False, not a date
 '1/1/1900'
 else Convert(datetime, somefield, 110)
 end as ConvertedDate , Somefield
 from @validdatetest
 where works = 1

It is obvious by looking at my example, that some of the values inserted into the temp table @validDateTest are not dates. The code below the insert statements will Convert only the values which IsDate says are dates, not all of the values as I have a where condition in the query.  The values that will Convert without giving me an error have a works value of 1. If you run this query, you will see the difference in the values returned by IsDate, and the values I have provided that work without returning an error.

 Select Somefield, isdate(somefield)as IsDateValue, works
 from @ValidDateTest

Here are the results

Covertresult

Now if you run the query listed above and take off the where condition

 Select case when isdate(somefield) = 0 then --False, not a date
 '1/1/1900'
 else Convert(datetime, somefield, 110)
 end as ConvertedDate , Somefield
 from @validdatetest

You’ll get this error
Msg 241, Level 16, State 1, Line 21
Conversion failed when converting date and/or time from character string.

Casting Around for Bad IsDate solution

If you are fishing around for a solution, the simplicity of this will probably make you smack your desk. Now ideally, the source system should be fixed, as it really should be making sure that the dates are valid. I can’t make that happen. But I do have a solution to resolve this problem, just Cast it. Cast and Convert are on the same page in Microsoft online help and a lot of the time you might think they are synonymous, but they don’t work the same way.

 Select case when isdate(somefield) = 0 then --False, not a date
 '1/1/1900'
 else Cast (somefield as datetime)
 end as ConvertedDate , Somefield
 from @validdatetest

This works with no errors whatsoever. Simple. Easy. If you ever run into this, I hope this saves you some time.

Yours Always,
Ginger Grant
Data aficionado et SQL Raconteur

5 comments on “When IsDate Does Not Work

  1. Gary

    So get this – both CONVERT and CAST fail with my data when trying to change it to DATE format. But both work if doing it to TIMESTAMP. And both work if I double CAST or CONVERT or CAST(CONVERT… by first going to timestamp and then date. Very strange.

    1. Ginger Grant

      Gary —
      That’s so odd! I’ve never seen that. Can anyone on the internets explain why it would work that way?

      –Ginger

    2. Dee

      I have a similar situation. Could you post the exact statement you have used.

  2. 1111

    IsDate only tells you if it is possible to convert to one of the date types – not which one, not with which format – it’s pretty useless to the average user in that respect!

Leave a Reply