SQL Server ISDATE Function


By:

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 simple example

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
ISDATE with null values

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
ISDATE invalid values

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
ISDATE with literal month

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
time with 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
SELECT @@LANGUAGE

In order to check all the languages and the dateformat used in SQL Server, use the sp_helplanguage system stored procedure.

sp_helplanguage
sp_helplanguage system stored procedure

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
invalid date with 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
ISDATE with dataformat dmy

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


Last Update: 1/4/2022




Comments For This Article





download














get free sql tips
agree to terms