I got the below email from one of my teammate on ISDATE() function.

date-email

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

date data type