Export (0) Print
Expand All
Expand Minimize
3 out of 14 rated this helpful - Rate this topic

ISDATE (Transact-SQL)

Determines whether an input expression is a valid date.

Topic link icon Transact-SQL Syntax Conventions


ISDATE ( expression )
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.

ms187347.note(en-US,SQL.90).gifNote:
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.

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

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                   
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.