sql-server-2008t-sqlsql-server-2012datetime2

Is there a function like isdate() for datetime2?

I know there is a function called ISDATE to validate DATETIME columns, but it works only for the SMALLDATETIME and DATETIME types.

Is there a similar way to validate the new data type DATETIME2 in SQL Server 2008 and 2012?

Solution

  • In SQL Server 2012, you can use TRY_CONVERT:

    SELECT TRY_CONVERT(DATETIME2, '2012-02-02 13:42:55.2323623'),
       TRY_CONVERT(DATETIME2, '2012-02-31 13:42:55.2323623');

    Results:

    2012-02-02 13:42:55.2323623    NULL

    Or TRY_PARSE:

    SELECT TRY_PARSE('2012-02-02 13:42:55.2323623' AS DATETIME2),
       TRY_PARSE('2012-02-31 13:42:55.2323623' AS DATETIME2);

    (Same results.)

    Sorry that I don't have a clever answer for you for < SQL Server 2012. You could, I guess, say

    SELECT ISDATE(LEFT('2012-02-02 13:42:55.2323623', 23));

    But that feels dirty.

    TRY_CONVERT documentation on Microsoft Docs
    TRY_PARSE documentation on Microsoft Docs