SQL Reloaded: Oracle vs. SQL Server (Date Validation)

When loading data into a table or even when performing date operations with text attributes that store date values, it’s mandatory to check whether the values represent a valid date, otherwise an error will be thrown, fact that could have negative impact on your logic. SQL Server provides the IsDate function for this purpose, the function returning a 1 if the value is a date, and 0 otherwise, though, as always, there is a catch or two. First of all the valid date refers only to the date format expected/supported by the database, for example SQL Server supports the ISO 8601 formats (YYYYMMDD, YYYY-MM-DD, YYYY-MM or YYYY) together with (independent of) the date format set using the DATEFORMAT or SET LANGUAGE, thus based on the existing settings ‘25-10-2009’ could be a valid date while ‘10-25-2009’ is not even if the interpretation of the respective value raises no doubt. In addition to shorter formats like YYYY-MM or YYYY, the date formats could include leading spaces (e.g. ‘ 2009-10- 25’) fact that could create problems when using the data outside SQL Server, therefore it’s a good idea to check whether the date is strictly in the requested format and eventually convert the various date types to a single supported format. 

 
Here are several examples with the IsDate function: 

 

-- SQL Server: IsDate function examples 
SELECT IsDate('20091025') Example1 --(ISO 8601) 
, IsDate('2009-10-25') Example2 --(ISO 8601) 
, IsDate('2009-10') Example3 --(ISO 8601) 
, IsDate('2009') Example4 -- (ISO 8601) 
, IsDate('25-10-2009') Example5 
, IsDate('10.25-2009') Example6 
, IsDate('10-25-2009') Example7 
, IsDate(' 10-25- 2009') Example8 
, IsDate('25-OCT-2009') Example9 
, IsDate(NULL) Example10 

  

There are two interesting points: the IsDate doesn’t recognize the ‘2009-10’ value as date even if it’s a ISO format, and it recognizes ‘10.25-2009’ as date. A NULL value is not recognized as a valid date, therefore if other behavior is expected the NULL case needs to be handled. 

 
Oracle doesn’t provide a similar functionality like the IsDate function existing in Oracle, though something could be done in this direction – create a function that attempts the conversion to a date and return a 0 in case an error is caught, a 1 otherwise. Because Oracle doesn’t support a boolean or bit data type a number was preferred in order to simulate the same behavior as the SQL Server built-in function. Unfortunately if it’s attempted to create a function like the below one, function’s behavior might not be the expected one. 

  

--Oracle: IsDate implementation (limited use) 
CREATE OR REPLACE FUNCTION IsDate ( 
sDate varchar2 ) RETURN 
number IS result_value date; 
BEGIN 
result_value:= Cast(sDate as date);
       RETURN CASE WHEN sDate IS NULL THEN 0 ELSE 1 END; 
       EXCEPTION 
       WHEN OTHERS THEN       

RETURN 0; 
END ; 

 Here are the above examples but this time in Oracle: 

-- Oracle: IsDate function examples 
SELECT IsDate('20091025') Example1 --(ISO 8601) 
, IsDate('2009-10-25') Example2 --(ISO 8601) 
, IsDate('2009-10') Example3 --(ISO 8601) 
, IsDate('2009') Example4 -- (ISO 8601) 
, IsDate('25-10-2009') Example5 
, IsDate('10.25-2009') Example6 
, IsDate('10-25-2009') Example7 
, IsDate(' 10-25- 2009') Example8 
, IsDate('25-OCT-2009') Example9 
, IsDate(NULL) Example10 
FROM DUAL 

The above function could be modified to use the TO_DATE function in order to convert explicitly the string value to a date by hard-coding the expected format. On the other side the expected format could be provided as parameter like in the below implementation, gaining thus flexibility.

--Oracle: IsDateFormat implementation 
CREATE OR REPLACE FUNCTION IsDateFormat ( 
   sDate varchar2 
, sFormat varchar2) RETURN 
number IS result_value date; 
BEGIN 
result_value:= to_date(sDate, sFormat);
      RETURN CASE WHEN sDate IS NULL THEN 0 ELSE 1 END; 
      EXCEPTION 
      WHEN OTHERS THEN 
      RETURN 0; 
END ; 

 Here are the same examples as above, this time with the expected format provided explicitly: 

 

-- Oracle: IsDateFormat function examples 
SELECT IsDateFormat('20091025', 'YYYYMMDD') Example1 --(ISO 8601) 
, IsDateFormat('2009-10-25', 'YYYY-MM-DD') Example2 --(ISO 8601) 
, IsDateFormat('2009-10', 'YYYY-MM') Example3 --(ISO 8601) 
, IsDateFormat('2009', 'YYYY') Example4 -- (ISO 8601) 
, IsDateFormat('25-10-2009', 'DD-MM-YYYY') Example5 
, IsDateFormat('10.25-2009', 'MM-DD-YYYY') Example6 
, IsDateFormat('10-25-2009', 'MM-DD-YYYY') Example7 
, IsDateFormat(' 10-25- 2009', 'MM-DD-YYYY') Example8 
, IsDateFormat('25-OCT-2009', 'DD-MON-YYYY') Example9 
, IsDateFormat(NULL, 'YYYY-MM-DD') Example10 
FROM DUAL