By: Daniel Calbimonte
The ISDATE function returns 1 if the parameter value is a valid date and 0 if it is not a valid date.
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.
Related Articles
- How to Get Current Date in SQL Server
- Mimic timestamp behavior of other database platforms to store last modified date
- SQL Convert Date to YYYYMMDD
- SQL Server DIFFERENCE Function
- SQL Server CONCAT Function
- Format SQL Server Dates with FORMAT Function
- DATEDIFF SQL Server Function
