In SQL Server, you can use the ISDATE() function to check if a value is a valid date.
To be more specific, this function only checks whether the value is a valid date, time, or datetime value, but not a datetime2 value. If you provide a datetime2 value, ISDATE() will tell you it’s not a date (it will return 0).
This article contains examples of this function.
Syntax
First, here’s the syntax:
ISDATE ( expression )
Where expression is the expression to be tested.
Example 1 – Valid Date
Here’s an example using a valid expression:
SELECT ISDATE('2000-01-01') AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
This returns 1, which means it’s a valid date, time, or datetime value.
Example 2 – Invalid Date
Here’s an example with an invalid expression:
SELECT ISDATE('2000-01-01 00:00:00.0000000') AS Result;
Result:
+----------+ | Result | |----------| | 0 | +----------+
This returns 0, which means it’s not a valid date, time, or datetime value.
Example 3 – Using the Return Value
You can use a conditional statement to make use of the return value (rather than simply display either 0 or 1).
Here’s a basic example where we simply print Valid Date or Invalid Date, depending on whether the return value is 1 or 0:
IF ISDATE('2000-01-01') = 1
PRINT 'Valid Date'
ELSE
PRINT 'Invalid Date';
Result:
Valid Date
Example 4 – Your LANGUAGE Settings
The return value of ISDATE() depends on your LANGUAGE and DATEFORMAT settings.
Here’s an example to demonstrate how the same value can return different results depending on the LANGUAGE setting used.
British
SET LANGUAGE British;
SELECT ISDATE('20/01/2000') AS '20/01/2000 in British';
Result:
Changed language setting to British. +-------------------------+ | 20/01/2000 in British | |-------------------------| | 1 | +-------------------------+
us_english
SET LANGUAGE us_english;
SELECT ISDATE('20/01/2000') AS '20/01/2000 in us_english';
Result:
Changed language setting to us_english. +----------------------------+ | 20/01/2000 in us_english | |----------------------------| | 0 | +----------------------------+
This happens because British uses the dd/MM/yyyy format while us_english uses MM/dd/yyy.
Example 5 – Your DATEFORMAT Settings
As mentioned, the return value of ISDATE() also depends on your DATEFORMAT settings.
Here’s an example to demonstrate how the same value can return different results depending on the DATEFORMAT setting used.
dmy
SET DATEFORMAT dmy;
SELECT ISDATE('20/01/2000') AS '20/01/2000 while DATEFORMAT is dmy';
Result:
+--------------------------------------+ | 20/01/2000 while DATEFORMAT is dmy | |--------------------------------------| | 1 | +--------------------------------------+
mdy
SET DATEFORMAT mdy;
SELECT ISDATE('20/01/2000') AS '20/01/2000 while DATEFORMAT is mdy';
Result:
+--------------------------------------+ | 20/01/2000 while DATEFORMAT is mdy | |--------------------------------------| | 0 | +--------------------------------------+