T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL ISDATE() Function



The T-SQL (Transact-SQL) ISDATE() function returns 1 if the expression is a valid datetime value. Otherwise, it returns 0.

Syntax

ISDATE(expression)

Parameters

expression Required. Specify a character string or expression to check whether it is a date. It must be less than 4000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for this function.

Return Value

Returns 1 if the expression is a valid datetime value, else returns 0.

Example 1:

The example below shows the usage of ISDATE() function.

SELECT ISDATE('2019-10-25');
Result: 1

SELECT ISDATE('2019-10-25 08:10:25');
Result: 1

SELECT ISDATE('2019-10-25 08:10:25.123');
Result: 1

SELECT ISDATE('08:10:25.123');
Result: 1

SELECT ISDATE('Hello World');
Result: 0

SELECT ISDATE(123);
Result: 0

SELECT ISDATE(NULL);
Result: 0

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate_of_Joining
1JohnLondon252018-05-25
2MarryNew York24NULL
3JoParis272019-06-09
4KimAmsterdam30NULL
5RameshNew Delhi282019-10-25
6SureshMumbai282021-12-26

The statement given below can be used to check if a record of Date_of_Joining column is a valid date or not.

SELECT *, ISDATE(Date_of_Joining) AS ISDATE_Value FROM Employee;

This will produce the result as shown below:

EmpIDNameCityAgeDate_of_JoiningISDATE_Value
1JohnLondon252018-05-251
2MarryNew York24NULL0
3JoParis272019-06-091
4KimAmsterdam30NULL0
5RameshNew Delhi282019-10-251
6SureshMumbai282021-12-261

❮ T-SQL Functions