IsDate() function returns wrong results

Question

IsDate() function returns wrong results

seaport

SSCrazy

Points: 2491
More actions
April 8, 2015 at 9:36 am

#316418

All dates in codes below are valid dates, at least for me. But SQL identifies as not date, although some dates works for the Convert function.
SELECT isdate('04/01/200'), isdate('04/01/0200'), isdate('200-04-01'),isdate('0200-04-01'), ISDATE('01/01/0001')
--select convert(date,'04/01/200') -- error
select convert(date,'04/01/0200')
--select convert(date,'200-04-01') -- error
select convert(date,'0200-04-01')
Anyone knows the limitation of the IsDate function?
I got the same results in SQL 2008R2 and 2014.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

I suspect this is because the ISDATE function has been around since before we had the date datatype and the values you posted here are not valid as datetime. Once the date datatype came around we could get dates earlier than 1753. I am guessing that the ISDATE function didn't get updated. You will notice that it is unable to convert those values to datetime which I am guessing is what is happening behind the scenes in that function.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323482 More actions · Answer 2

the two values you posted will convert to datetime2, but not date or datetime; maybe that's it?

--select convert(date,'04/01/200') -- error

select convert(datetime2,'04/01/0200')

--select convert(date,'200-04-01') -- error

select convert(datetime2,'0200-04-01')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

Lowell (4/8/2015)
the two values you posted will convert to datetime2, but not date or datetime; maybe that's it?
--select convert(date,'04/01/200') -- error
select convert(datetime2,'04/01/0200')
--select convert(date,'200-04-01') -- error
select convert(datetime2,'0200-04-01')

They both cast to date just fine on 2008r2.

--select convert(date,'04/01/200') -- error

select convert(date,'04/01/0200')

--select convert(date,'200-04-01') -- error

select convert(date,'0200-04-01')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

seaport SSCrazy Points: 2491 More actions · Answer 4

Sean Lange (4/8/2015)
I suspect this is because the ISDATE function has been around since before we had the date datatype and the values you posted here are not valid as datetime. Once the date datatype came around we could get dates earlier than 1753. I am guessing that the ISDATE function didn't get updated. You will notice that it is unable to convert those values to datetime which I am guessing is what is happening behind the scenes in that function.

Sean,

Great post. Following your lead I found that 1/1/1753 might be used as the minimum date in the IsDate(). Any date before that date is considered "Not Date". Check out the code below.

[Code]

SELECT isdate('01/01/1753'), isdate('12/31/1752')

[/Code]

Here is some interesting reading.

http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server

seaport SSCrazy Points: 2491 More actions · Answer 5

I might have confused others with the code below. Its purpose is to show that SQL Convert function can handle date in Year 200, while ISDATE function cannot.

select convert(date,'04/01/0200')

select convert(date,'0200-04-01')