SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.
This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.
Anyway, below are three options we can use to check whether a datetime2 value is a valid date.
The Problem
First, let’s take a quick look at the problem:
SELECT
ISDATE( '2035-01-30 17:15:45.123' ) AS datetime,
ISDATE( '2035-01-30 17:15:45.1234567' ) AS datetime2;Result:
datetime datetime2 -------------- --------- 1 0
Here, ISDATE() correctly identified the datetime value as being a date, but not the datetime2 value. All I did was add more fractional seconds precision to the second value, which made it a datetime2 value.
It’s actually a string literal, but this string literal has seven fractional seconds precision, which isn’t supported by the datetime type. The datetime2 type does support seven fractional seconds precision, and so that’s why I’m calling it a datetime2 value, and that’s presumably why ISDATE() tells us that it’s not a date.
In any case, the ISDATE() function provides misleading results here, because we can all see that the second value is in fact a date, but ISDATE() tells us it’s not.
Microsoft acknowledges this, and its documentation for ISDATE() explicitly specifies:
ISDATE returns 0 if the expression is a datetime2 value.
So what are we to do?
Fortunately, we can use the following options to provide ISDATE() like functionality for datetime2 values.
Option 1: Use the TRY_CONVERT() Function
One option is to use the TRY_CONVERT() function to attempt to convert the value to a datetime2 value. If the value can be converted it is returned. But if it can’t be converted, the function returns NULL. Therefore a return value of NULL will tell us that it’s not a valid date.
We can also wrap the TRY_CONVERT() in an IIF() function to specify what to output for each case (e.g. a 1 or a 0).
Example:
SELECT
IIF( TRY_CONVERT( DATETIME2, '2035-01-30 17:15:45.123' ) IS NULL, 0, 1 ) AS datetime,
IIF( TRY_CONVERT( DATETIME2, '2035-01-30 17:15:45.1234567' ) IS NULL, 0, 1 ) AS datetime2;Result:
datetime datetime2 -------------- --------- 1 1
This time we get 1 for both dates (instead of 0 for the second, like in the previous example using the ISDATE() function).
Just to be clear, here’s an example without the IIF() function that compares two datetime2-like values:
SELECT
TRY_CONVERT( DATETIME2, '2035-01-30 17:15:45.1234567' ) AS Result_1,
TRY_CONVERT( DATETIME2, '2035-30-01 17:15:45.1234567' ) AS Result_2;Result:
Result_1 Result_2 ------------------------------ -------- 2035-01-30 17:15:45.1234567 NULL
In this case, both values resemble datetime2 values, but the first value is a valid date and the second one isn’t. The TRY_CONVERT() function returned the one that could be converted to datetime2, and returned NULL for the one that couldn’t.
Here’s what we get when we wrap them in the IIF() function:
SELECT
IIF( TRY_CONVERT( DATETIME2, '2035-01-30 17:15:45.1234567' ) IS NULL, 0, 1 ) AS Result_1,
IIF( TRY_CONVERT( DATETIME2, '2035-30-01 17:15:45.1234567' ) IS NULL, 0, 1 ) AS Result_2;Result:
Result_1 Result_2 -------------- -------- 1 0
Option 2: Use the TRY_CAST() Function
The TRY_CAST() function works just like TRY_CONVERT(), but with a slightly different syntax.
Example:
SELECT
IIF( TRY_CAST( '2035-01-30 17:15:45.123' AS DATETIME2 ) IS NULL, 0, 1 ) AS datetime,
IIF( TRY_CAST( '2035-01-30 17:15:45.1234567' AS DATETIME2 ) IS NULL, 0, 1 ) AS datetime2;Result:
datetime datetime2 -------------- --------- 1 1
Same result as with TRY_CONVERT().
Option 3: Use the TRY_PARSE() Function
Another way to do it is with the TRY_PARSE() function:
SELECT
IIF( TRY_PARSE( '2035-01-30 17:15:45.123' AS DATETIME2 ) IS NULL, 0, 1 ) AS datetime,
IIF( TRY_PARSE( '2035-01-30 17:15:45.1234567' AS DATETIME2 ) IS NULL, 0, 1 ) AS datetime2;Result:
datetime datetime2 -------------- --------- 1 1
Important Consideration
One thing to be mindful of is that TRY_PARSE() can recognise some date formats that TRY_CAST() and TRY_CONVERT() can’t. Therefore, we can get different results, depending on what value we provide and which function we use.
Here’s an example of what I mean:
SELECT
IIF( TRY_PARSE( 'Thursday 19 Jan 2023' AS DATETIME2 ) IS NULL, 0, 1 ) AS TRY_PARSE,
IIF( TRY_CAST( 'Thursday 19 Jan 2023' AS DATETIME2 ) IS NULL, 0, 1 ) AS TRY_CAST;Result:
TRY_PARSE TRY_CAST -------------- --------- 1 0
The TRY_PARSE() function worked out that we provided a date, whereas TRY_CAST() didn’t.
Actually, had we used CAST() instead of TRY_CAST(), we would have received an error, due to the value not being able to be converted to a datetime2.
For more information on the difference between these functions, see PARSE() vs CAST() vs CONVERT() in SQL Server: What’s the Difference?. While that article deals with the non-TRY_ versions of these functions, the general discussion applies to both.