Question: What is the Biggest Limitation of ISDATE() Function?
Answer: I really wish the original question was asked in this format. Instead in the last meeting, the question was actually asked was following:
Why do the first query returns 1 and second query returns 0?
Query 1:
SELECT ISDATE('6666-04-30') IsValidDate
Query 2:
SELECT ISDATE('1111-04-30') IsValidDate
Honestly, the reason, I do not like questions such as this because they really are just dependent on people’s knowing very special information. I rather prefer to ask question which gives more opportunity to candidate to be correct and feel more comfortable.
I personally believe there are two biggest limitations of the ISDATE () function.
Limitation 1: When we are using four digit format in yyyy format, it supports only year 1582 to 9999.
Limitation 2: Two digit years, sometimes can be confusing as it does not represent which century they represent.
If we ask candidates to answer the limitation of ISDATE () function they can for sure think of some more valid answers. Once they know the answer they can list them in the order of priority. This way they they have more chance to demonstrate their knowledge.
Meanwhile the limitation 1, listed above is the reason why Query 1 gives us correct answer and query 2 gives us incorrect answer.
Let me know what you think of this question. I would like to know your opinion in the comments section.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
I am using SSMS2012 (11.0.5388.0), which returns ‘1753-Jan-01’ as valid date if it is ‘1752-Dec-31’ it returns in-valid date.
According to MSDN Date range is supports from January 1, 1753, through December 31, 9999
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017.
Please correct me if I am wrong
Yes this is correct as per my knowledge also..
Select ISDATE(‘1583-04-30’) IsValiDate
this is returning 0 , as your answer this should be return 1 but I’m getting 0 ?? what is the reason??
Julian calendar vs. Gregorian calendar. ISDATE() handles dates in the Gregorian calendar which was _created_ in October 1582. The research as to why and any other details of the two calendars is left as an exercise for the curious.
if we execute
select ISDATE(1807-28-26) isvaliddate
its will return 1 but it’s not correct valid date.
@Ashish Kumar
I think you should pass date value in single quote
then check, you will get 0…
I have checked limitation – 1 you mentioned years range 1582 to 9999. But it should 1753 to 9999.