ISDate Function To Check Whether String is Date or Not

While working on my project there was a specific need to find out that input parameter is a valid date or not.

To project specific need To_date function was not enough as it throws error if the string is not valid date.

Second project asked to handle different date format i.e

YYYYMMDD, YYYYDDMM, YYYY-MM-DD, YYYY-MM-DD, DD/MM/YYYY, MM/DD/YYYY etc

Most of the date format TO_Date function easily handles but there are few which it cannot  i.e

SELECT TO_DATE('12/31/2010', 'YYYY-MM-DD') "to date" FROM DUMMY;
SELECT TO_DATE('31/12/2010', 'YYYY-MM-DD') "to date" FROM DUMMY;

Result
Could not execute 'SELECT TO_DATE('31/12/2010', 'YYYY-MM-DD') "to date" FROM DUMMY'
[303]: invalid DATE, TIME or TIMESTAMP value: Error while parsing '31/12/2010' in format 'YYYY-MM-DD' as DATE/TIME at function to_date() (at pos 7) 

I will always recommend to use SAP standard function, but sometime you have to be creative to get job done.

As my project demanded many more date format I have to create something similar to ISDate() UDF which will return not 0 or 1 but actual date and if the string is not a valid date then ‘0002-01-01’

Again let me stress that always use SAP Standard Function, only when your project demand something beyond standard then create something which will satisfy project needs.

In my case I handle string to date my altering M_TIME_Dimension table

ALTER TABLE "_SYS_BI"."M_TIME_DIMENSION" ADD ("DATEFORMAT" NVARCHAR(10) GENERATED ALWAYS AS TO_CHAR("DATE_SQL",	 'YYYY/MM/DD'));
ALTER TABLE "_SYS_BI"."M_TIME_DIMENSION" ADD ("DATEFORMAT1" NVARCHAR(10) GENERATED ALWAYS AS TO_CHAR("DATE_SQL", 'MM/DD/YYYY'));
ALTER TABLE "_SYS_BI"."M_TIME_DIMENSION" ADD ("DATEFORMAT2" NVARCHAR(10) GENERATED ALWAYS AS TO_CHAR("DATE_SQL", 'DD.MM.YYYY'));

I have added few but you can add more date format as per your project need.

Once you have altered the table, you can use below mentioned script to created isdate() function module.

DROP FUNCTION ISDATE;
CREATE FUNCTION ISDATE (inputval NVARCHAR(100))
RETURNS RETURNVAL DATE
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE DT date;
DECLARE VAL NVARCHAR(10);
DECLARE VCOUNT INT;

 select count(*) into VCOUNT from "_SYS_BI"."M_TIME_DIMENSION"
 where CONTAINS(*, :inputval) ;
 
IF VCOUNT > 0 THEN 

 select top 1 DISTINCT CAST(DATE_SQL AS DATE) into DT from "_SYS_BI"."M_TIME_DIMENSION"  where CONTAINS(*,:inputval , EXACT);
 
    RETURNVAL := :DT;

ELSE

   DECLARE EXIT HANDLER FOR SQLEXCEPTION

        BEGIN

   RETURNVAL := '0002-01-01';

        END;

   select CAST(:inputval AS DATE) into DT from dummy;
    RETURNVAL := :DT;
END IF;

END;

Check the UDF

select ISDATE('abc') from dummy;
Result
0002-01-01

select ISDATE('20/04/2017') from dummy;
Result
2017-04-20

If you don’t want to alter M_TIME_Dimension table then you can add alter logic in your function.

The only bug in this function is that, if you have MM or DD less than 12 then right 2 character will be treated as month.

for example

09 can be month or it can be day, similarly 05 can be month or day but in below example it will treat 05 as month.

Any suggestions to make this better are always welcome.