SQL Server ISDATE() Function

In SQL Server, the ISDATE() function returns 1 if the input expression is a valid datetime value, else it returns 0.

ISDATE(input_expression)

Parameters

input_expression: It is a string or an expression that can be converted to a string. It must be less than 4000 characters.

Note: The datetime range is 1753-01- 01 to 9999-12-31 and date range is 0001-01-01 to 9999-12-31.

Return Value

Returns integer value 0 or 1. 0 indicates the input expression is not a date and return value. 1 indicates that the input expression is a valid date or datetime value.

If input_expression is a datetime2 value, then ISDATE() returns 0.

Check for Valid Date String

In the following example, the ISDATE() validates whether the specified date string is a valid date or not. It returns 1 if valid date otherwise returns 0.

Example: Validate Date String

SELECT ISDATE ('2022-10-20') AS IsValidDate

Check for Valid DateTime String

In the following example, the ISDATE() function validates whether the specified string is a valid datetime value or not.

Example: Validate DateTime String

SELECT ISDATE ('2022-10-20 10:10:20') AS IsValidDate

In the following example, a string, an integer value, a Boolean and Boolean values are passed to the ISDATE() function which returns a 0 (False).

Example: Invalid Dates

SELECT ISDATE ('Hello') AS IsStrDate, ISDATE(1234) AS IsIntdate, ISDATE('FALSE') AS IsBolDate, ISDATE(0) IsBolintDate

Check for Valid DateTime Format

In the following example, an invalid date format is passed to the ISDATE() function.

Example:

SELECT ISDATE ('2022-20-10') AS IsValidDate

The above query returns 0 because of invalid date format as per the server setting. The return value of ISDATE() depends on the server settings set by SET DATEFORMAT, SET LANGUAGE, and the default language of the server.

To check the current setting of the SQL Server, use the following statement

Example: Server Setting

DBCC useroptions

As you can see, the valid date format for the server is mdy and so the ISDATE() function returned 0. Now, change the date format to dmy, as shown below.

Example: Change Date Format

SET DATEFORMAT dmy;

Now, execute the following SQL again. This time the ISDATE() function returns 1.

Example:

SELECT ISDATE ('2022-20-10') AS IsValidDate

Note: You can change the date format to dmy, ydm, ymd from standard mdy format.