Determines whether an input expression is a valid date.
Transact-SQL Syntax Conventions
ISDATE ( expression )
Is an expression to be validated as a date. expression is any expression, except text, ntext, and image expressions, that can be implicitly converted to nvarchar.
int
ISDATE is deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
The return value of ISDATE may be affected by LANGUAGE and DATEFORMAT settings.
For examples of templates for which ISDATE will return 1, see the Input/Output column of the "Arguments" section of CAST and CONVERT.
ISDATE returns 1 if the input expression is a valid date; otherwise, it returns 0. The following table shows the return values for a selection of examples.
NULL
0
Abc
100, -100, 100 a, or 100.00
.01
-100.1234e-123
.231e90
$100.12345, - $100.12345, or $-1000.123
as100 or 1a00
1995-10-1, 1/20/95, 1995-10-1 12:00pm, Feb 7 1995 11:00pm, 1995-10-1, or 1/23/95
1
13/43/3425 or 1995-10-1a
$1000, $100, or $100 a
The following example checks the @datestring local variable for valid date data.
@datestring
DECLARE @datestring varchar(8) SET @datestring = '12/21/98' SELECT ISDATE(@datestring)
Here is the result set.
----------- 1
The following example creates the test_dates table and inserts two values. ISDATE is used to determine whether the values in the columns are dates.
test
dates
ISDATE
USE tempdb CREATE TABLE test_dates (Col_1 varchar(15), Col_2 datetime) GO INSERT INTO test_dates VALUES ('abc', 'July 13, 1998') GO SELECT ISDATE(Col_1) AS Col_1, ISDATE(Col_2) AS Col_2 FROM test_dates
Col_1 Col_2 ----------------- -------------------- 0 1