Overview
The ISDATE function returns 1 if the parameter value is a valid date and 0 if it is not a valid date.
Explanation
Syntax
ISDATE(value)Parameters
- Value – Can be a date, number or string to be evaluated.
Simple ISDATE Example
The following example will show the value of 1 if the date provided in the parameter is valid.
SELECT ISDATE('10-5-23') as isDate
ISDATE with NULL values
When the parameter is NULL, the ISDATE function will return the value of 0 which means that the NULL value is not a valid date.
SELECT ISDATE(NULL) as isDate
Invalid dates with ISDATE
The following example, has an invalid month equal to 13. The value returned by the function is 0 (invalid).
SELECT ISDATE('13-5-23') as isDate
Literal month with the ISDATE function
If you use the literal name for the month, the ISDATE will return the result as valid (1).
SELECT ISDATE('February 2 2021') as isDate
Time with the ISDATE function
If a time value is used as an input parameter the ISDATE will return the result as valid (1).
SELECT ISDATE('5:32:54') as isDate
DATEFORMAT and ISDATE
There is a dependency between the format date and the ISDATE. For example, in France, the date format is dmy (day, month, year), while in the USA it is mdy (month, day, year).
That is why, the ISDATE will return a different value according to the DATEFORMAT in SQL Server.
To verify the current language used by your SQL Server use the following.
SELECT @@LANGUAGE as language
In order to check all the languages and the dateformat used in SQL Server, use the sp_helplanguage system stored procedure.
sp_helplanguage
You will be able to see the language and the corresponding dateformat.
In us_english, the following example is invalid:
SELECT ISDATE('20-03-2003') as isDate
However, if we change the DATEFORMAT to dmy, the date will be valid.
SET DATEFORMAT dmy
GO
SELECT ISDATE('20-03-2003') as isDate
This is because in English, the format is mdy, and 20 is an invalid month, but using the dmy date format, 20 is the day and not the month, and therefore it is valid.
Additional Information
