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. The question is: is there a similar way to validate the new data type datetime2 in sql server 2008 and 2012?

Answers


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 < 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 MSDN TRY_PARSE documentation on MSDN


Need Your Help

converting a JSON string back to an array

python arrays json ajax

I'm passing a 2-dimensional array of float values to my views.py via ajax. My ajax call looks like this:

Render .jade with json from ajax GET

jquery ajax express pug render

I have layout for our jQuery, Node, Jade setup to test our api's on a local server.