How to Use the ISDATE() Function in SQL Server: A Complete Guide

Syntax:

<span class="pln">
ISDATE</span><span class="pun">()</span><span class="pln"> 
</span>

Checks if the given value, is a valid date, time, or datetime. Returns 1 for success, 0 for failure.

Code:

<span class="pln">
</span><span class="typ">Select</span><span class="pln"> ISDATE</span><span class="pun">(</span><span class="str">'RABMO'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="lit">0</span><span class="pln">
</span><span class="typ">Select</span><span class="pln"> ISDATE</span><span class="pun">(</span><span class="typ">Getdate</span><span class="pun">())</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="lit">1</span><span class="pln">
</span><span class="typ">Select</span><span class="pln"> ISDATE</span><span class="pun">(</span><span class="str">'2019-08-31 21:02:04.167'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="lit">1</span><span class="pln">
</span>

Output:

The above code will produce the following result-

<span class="pln">
</span><span class="lit">0</span><span class="pln">
</span><span class="lit">1</span><span class="pln">
</span><span class="lit">1</span><span class="pln">
</span>

Note: For datetime2 values, IsDate returns ZERO.

<span class="pln">
</span><span class="typ">Select</span><span class="pln"> ISDATE</span><span class="pun">(</span><span class="str">'2019-09-01 11:34:21.1918447'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="lit">0.</span><span class="pln">
</span>