• An interesting question - especially because the correct answer (and it is correct, as can be easily proven by running the code) is not what the documentation predicts.

    The documentation on ISDATE (linked in the answer explanation) says: "expression -- Is a character string or expression that can be converted to a character string."

    The documentation on converting (http://msdn.microsoft.com/en-us/library/ms187928.aspx) says that implicit conversions from date to character strings (char, varchar. nchar, and nvarchar) are allowed. As can also be easily verified by running the code below.

    So according to the documentation, the ISDATE should convert the date to character string, then test if it's a valid date. Just as it does, and always has done, when fed a datetime argument.

    The text of the error message indicates that this is a deliberate change of behaviour between the date data type and the old datetime data type, so my guess is that this is a documentation bug.

    EDIT: Forgot to paste in the demo code...

    -- Show that date will implicitly convert to string

    DECLARE @dt1 DATE = '20120828';

    SELECT RTRIM(@dt1);

    go

    -- Show that datetime will work in ISDATE (even on SQL 2008 and SQL 2012)

    DECLARE @dt1 DATETIME = '20120828';

    SELECT ISDATE(@dt1);

    go


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/