ISDATE (Transact-SQL)

Determines whether an input expression is a valid date.

Transact-SQL Syntax Conventions

Syntax

ISDATE ( expression )

Arguments

• 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.

  Note

  If expression is of type varchar, the value is converted to nvarchar(4000). If a larger value that would result in truncation is passed, SQL Server 2005 generates an error.

Return Types

int

Remarks

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.

Column value (varchar)	ISDATE return value
NULL	0
Abc	0
100, -100, 100 a, or 100.00	0
.01	0
-100.1234e-123	0
.231e90	0
$100.12345, - $100.12345, or $-1000.123	0
as100 or 1a00	0
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	0
$1000, $100, or $100 a	0

Examples

A. Using ISDATE to check a variable

The following example checks the @datestring local variable for valid date data.

DECLARE @datestring varchar(8)
SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)

Here is the result set.

----------- 
1           

B. Using ISDATE to check a column for dates

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.

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

Here is the result set.

Col_1                     Col_2               
-----------------         --------------------
0                         1                   

See Also

Reference

SET DATEFORMAT (Transact-SQL)
SET LANGUAGE (Transact-SQL)
char and varchar (Transact-SQL)
System Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance