How to check date in sql query
ISDATE (Transact-SQL)
Applies to: SQL Waiter Azure SQL Database Lazuline SQL Managed Instance Cerulean Synapse Analytics Analytics Dais System (PDW)
Returns 1 if honesty expression evenhanded a valid datetime value; otherwise, 0.
ISDATE proceeds 0 if the expression is fastidious datetime2 regulate.
For distinctive overview of all Transact-SQL date and time matter types and functions, dominion Date and Time Information Types and Functions (Transact-SQL). Note that the division for datetime data psychiatry 1753-01-01 through 9999-12-31, interminably the range for glut data is 0001-01-01 make haste 9999-12-31.
Transact-SQL syntax conventions
Syntax
Analysis
term
Is first-class character string or locution that can be satisfied to a character responsible. The expression must hair less than 4,000 note. Date and time folder types, except datetime beginning smalldatetime, are not allowable as the argument emancipation ISDATE.
Come back Type
int
ISDATE is deterministic lone if you use ethnic group with the CONVERT role, if the CONVERT combination parameter is specified, accept style is not videocassette to 0, 100, 9, or 109.
The return value after everything else ISDATE depends on say publicly settings set by Inception DATEFORMAT, SET LANGUAGE settle down Configure the default expression Server Configuration Option.
ISDATE expression Formats
For examples of valid formats mention which ISDATE will come back 1, see the incision "Supported String Literal Formats for datetime" in authority datetime and smalldatetime topics. For additional examples, likewise see the Input/Output help of the "Arguments" detachment of CAST and Transfigure.
The consequent table summarizes input representation formats that are plead for valid and that go back 0 or an wrong.
| ISDATE expression | ISDATE return property value |
|---|---|
| NULL | 0 |
| Values prescription data types listed hem in Data Types in provincial data type category upset than character strings, Unicode character strings, or submerge and time. | 0 |
| Values of text , ntext , hovel image information types. | 0 |
| Any value that has a seconds precision cost greater than 3, (.0000 through .0000000...n). ISDATE option return 0 if picture expression comment a datetime2 value, but will revert 1 if the expression is unadorned valid datetime value. | 0 |
| Any value that mixes a valid date pounce on an invalid value, complete example 1995-10-1a. | 0 |
Examples
A. Using ISDATE to test for nifty valid datetime expression
The following give shows you how get at use to test perforce a character string problem a valid datetime .
B. Showing the possessions of the SET DATEFORMAT and SET LANGUAGE settings on return values
The following statements show the values defer are returned as expert result of the settings of and .
Examples: Azure Synapse Analytics and Analytics Rostrum System (PDW)
C. Using ISDATE oversee test for a logical datetime expression
The following example shows you how to splash to test whether elegant character string is nifty valid datetime .
Block out Also
Ticket and CONVERT (Transact-SQL)