About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Sunday, February 14, 2010

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

Date IsDate SQL Server
       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

Date IsDate Oracle

      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

Date IsDateFormat Oracle  

No comments: