SQL Server ISDATE Function

Summary: in this tutorial, you will learn how to use the SQL Server ISDATE() function to check if a value is a valid DATE, TIME or DATETIME.

Introduction to SQL Server ISDATE() function

The ISDATE() function accepts an argument and returns 1 if that argument is a valid DATE, TIME, or DATETIME value; otherwise, it returns 0.

ISDATE(expression)
Code language: SQL (Structured Query Language) (sql)

The expression is a character string or expression that can resolve to a character string. Its length must be less than 4,000 characters.

The expression can be also a value of DATE or TIME, but it cannot be a value of DATETIME or SMALLDATETIME type.

If the expression is a DATETIME2 value, the function returns 0.

Notice that the range for DATETIME data is from 1753-01-01 to 9999-12-31, while the range for DATE data is from 0001-01-01 to 9999-12-31.

SQL Server ISDATE() function examples

Let’s take some examples of using the ISDATE() function

Using ISDATE() function to check a valid date expression

This example uses the ISDATE() function to test if a string is a valid DATE data:

SELECT 
    ISDATE('2020-06-15') is_date
Code language: SQL (Structured Query Language) (sql)

Here is the output:

is_date
-----------
1

(1 row affected)

See this example:

SELECT 
    ISDATE('2020-15-06') is_date
Code language: SQL (Structured Query Language) (sql)

The output is:

is_date
-----------
0

(1 row affected)

This ISDATE() function depends on the SET DATEFORMAT, SET LANGUAGE, and default language of the SQL Server.

To view the current setting, you execute the following command:

DBCC useroptions
Code language: SQL (Structured Query Language) (sql)

The current language setting is us_english. If you change the language to british:

SET LANGUAGE british;
Code language: SQL (Structured Query Language) (sql)

Then the ISDATE() will return a different result for the following example:

SELECT 
    ISDATE('2020-15-06') is_date
Code language: SQL (Structured Query Language) (sql)

The string '2020-15-06' is now considered as a valid date:

is_date
-----------
1

(1 row affected)        

Using ISDATE() function to check a valid datetime expression

The following example uses the ISDATE() function to test if the expression is a valid DATETIME value:

SELECT 
    ISDATE('2020-12-05 11:20:30') is_date
Code language: SQL (Structured Query Language) (sql)

Here is the output:

is_date
-----------
1

(1 row affected)

In this tutorial, you have learned how to use the SQL Server ISDATE() function to check whether an expression is a valid DATE, TIME, or DATETIME value.