ISDATE (Transact-SQL)
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.
.gif "Topic link icon")
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.
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 default language option. For examples, see example C.
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) |
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;