ISDATE (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Returns 1 if the expression is a valid datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). Note that the range for datetime data is 1753-01-01 through 9999-12-31, while the range for date data is 0001-01-01 through 9999-12-31.
Transact-SQL syntax conventions
Syntax
ISDATE ( expression )
Arguments
expression
Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.
Return Type
int
Remarks
ISDATE is deterministic only if you use it with the CONVERT function, if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.
The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.
ISDATE expression Formats
For examples of valid formats for which ISDATE will return 1, see the section "Supported String Literal Formats for datetime" in the datetime and smalldatetime topics. For additional examples, also see the Input/Output column of the "Arguments" section of CAST and CONVERT.
The following table summarizes input expression formats that are not valid and that return 0 or an error.
| ISDATE expression | ISDATE return value |
|---|---|
| NULL | 0 |
| Values of data types listed in Data Types in any data type category other than character strings, Unicode character strings, or date and time. | 0 |
| Values of text, ntext, or image data types. | 0 |
| Any value that has a seconds precision scale greater than 3, (.0000 through .0000000...n). ISDATE will return 0 if the expression is a datetime2 value, but will return 1 if the expression is a valid datetime value. | 0 |
| Any value that mixes a valid date with an invalid value, for example 1995-10-1a. | 0 |
Examples
A. Using ISDATE to test for a valid datetime expression
The following example shows you how to use ISDATE to test whether a character string is a valid datetime.
IF ISDATE('2009-05-12 10:19:41.177') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID';
B. Showing the effects of the SET DATEFORMAT and SET LANGUAGE settings on return values
The following statements show the values that are returned as a result of the settings of SET DATEFORMAT and SET LANGUAGE.
/* Use these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
/* Expression in mdy dateformat */
SELECT ISDATE('04/15/2008'); --Returns 1.
/* Expression in mdy dateformat */
SELECT ISDATE('04-15-2008'); --Returns 1.
/* Expression in mdy dateformat */
SELECT ISDATE('04.15.2008'); --Returns 1.
/* Expression in myd dateformat */
SELECT ISDATE('04/2008/15'); --Returns 1.
SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.
SET DATEFORMAT dmy;
SELECT ISDATE('15/04/2008'); --Returns 1.
SET DATEFORMAT dym;
SELECT ISDATE('15/2008/04'); --Returns 1.
SET DATEFORMAT ydm;
SELECT ISDATE('2008/15/04'); --Returns 1.
SET DATEFORMAT ymd;
SELECT ISDATE('2008/04/15'); --Returns 1.
SET LANGUAGE English;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET LANGUAGE Hungarian;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET LANGUAGE Swedish;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET LANGUAGE Italian;
SELECT ISDATE('2008/04/15'); --Returns 1.
/* Return to these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
C. Using ISDATE to test for a valid datetime expression
The following example shows you how to use ISDATE to test whether a character string is a valid datetime.
IF ISDATE('2009-05-12 10:19:41.177') = 1
SELECT 'VALID';
ELSE
SELECT 'INVALID';