CvtYYYY_MM_DD2Date

-- Convert a numeric date consisting of 3 Columns (4-digit year, 2-digit month, 2-digit day) into a real date
-- If Year, Month, Day are all *Zeros - 0001-01-01 is returned
-- If Year, Month, Day are all 9      - 9999-12-31 is returned
-- If Year, Month, Day deliver an invalid date - 8888-12-31 is returned
-- Otherwise the converted date is returned

Create Or Replace Function YourSchema/CvtYYYY_MM_DD2Date (
          Paryear Decimal(4, 0)  Default 0, 
          Parmonth Decimal(2, 0) Default 0,
          Parday Decimal(2, 0)   Default 0)
   Returns Date
   Language SQL
   Deterministic
   Reads SQL Data
   Called On Null Input
   Set Option DbgView = *Source
   Begin
      Declare GblMinDate     Date Constant '0001-01-01';
      Declare GbMaxDate      Date Constant '9999-12-31';
      Declare GblInvalidDate Date Constant '8888-12-31';
      
      Declare Continue Handler For SQLEXCEPTION Return GblInvalidDate;
      
      If Paryear = 0 And Parmonth = 0 And Parday = 0 
         Then Return GblMinDate;
      ElseIf Paryear = 9999 And Parmonth = 99 And Parday = 99
         Then Return GbMaxDate;
      Else Return  Date(Digits(Paryear) Concat Digits(Parmonth) Concat Digits(Parday) Concat '000000');
      End If;
   End;