Limitation of ISDATE() Function in Sql Server

Hey folks till now we have discussed Performance improvement of cursor SQL Server, User defined functions in sql, Kill Processes in sql server 2008, View in sql , default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

 

Today we will look into Limitation of ISDATE() function in Sql server.

ISDATE() function is used to check whether date is valid or not. If date is valid, this function will return 1 else return 0.

Let’s try to understand what is the Limitation of ISDATE() function in Sql Server using examples:

SELECT ISDATE('2999-04-30') IsValidDate   -- 1
SELECT ISDATE('1021-04-30') IsValidDate   -- 0


-- Result will be VALID
IF ISDATE('2009-05-12 10:19:41.177') = 1  
    SELECT 'VALID';  
ELSE  
    SELECT 'INVALID';
    
-- Result will be INVALID    
IF ISDATE('1009-03-2 10:19:41.177') = 1  
    SELECT 'VALID';  
ELSE  
    SELECT 'INVALID';

 

Note: When we are using four digit format in yyyy format, it supports only year 1582 to 9999.