ISDATE Function doesn’t validate “date” and “datetime2” data types

Last week I have done some R&D stuff reading SQL Server and as well as Oracle. Recently I have come up with issue how to validate Date column. Answer is very simple; Every SQL Server guy’s Answer is ISDATE function and my answer was same. But after few second, again have a complained we can’t use this function it gives error. I got surprised and ask him to send that error with table format,

• Column data type is – date (SQL Server 2008)
• Error,

Msg 8116, Level 16, State 1, Line 3
Argument data type date is invalid for argument 1 of isdate function.

Then I have created some sample T-SQL scripts and check the issue.

USE tempdb
GO
DECLARE @smalldatetime smalldatetime
SELECT @smalldatetime='01/01/2010'
SELECT ISDATE(@smalldatetime);
GO
DECLARE @date date
SELECT @date='01/01/2010'
SELECT ISDATE(@date); --Error
GO
DECLARE @datetime datetime
SELECT @datetime='01/01/2010'
SELECT ISDATE(@datetime);
GO
DECLARE @datetime2 datetime2
SELECT @datetime2='01/01/2010'
SELECT ISDATE(@datetime2); --Error
GO

Finally I realize the problem ISDATE function, only working datetime, smalldatetime data types. Funny thing is Microsoft did not update the ISDATE () function to support this data types. Even it’s not supported SQL SERVER 2008 R2. Simply it doesn’t validate “date” data type and “datetime2” data type. After checking details for this issue Microsoft promised to consider this in there next release (I didn’t check this issue using SQL Server new CTP1 release (”Denali”))