I got the below email from one of my teammate on ISDATE() function.
Thanks to him for asking me the question. I do not have any idea on this behaviour of sql server. I gone through the MSDN documentation and got to know, why SQL Server behaves like this.
select ISDATE(’08/16/013′) returns true because here it treats ’08/16/013′ as datetime type (where time part is ’00:00:00.000’ which is optional in datetime data type) But when you use select Convert(date, ’08/16/013′), ’08/16/013′ is not a valid date format, it is a valid datetime format.
Date data type supports only either 2 digits or 4 digits’ year. So instead you use
select Convert(date, ’08/16/13′) or select Convert(date, ’08/16/2013′).
Date data type supports only either 2 digit or 4 digit year string literals whereas for datetime data type there is no restriction like date type. The string literal will be number separator number separator number separator [time][time] and it validates based on what dateformat set for the instance.
Below is the excerpt from MSDN:
Supported String Literal Formats for date
