How to check whether the date is valid or not

[unnzteenz]

Hi,
I'm using a procedure in which i have to append the date by 1 unit, everytime the loop increases. I need to check whether the new processed date is valid or not. for eg: suppose my current date is 2007-02-28. on adding 1 it is giving me 2007-02-29. i hv to check whether it is a valid date or not thru a function. how can it be done..?? is there any inbuilt mechanism to check

 

[PHV]

You may trap the exception in (-1267) when using the DATE() function.
But 1+DATE('2007-02-28') simply gives 2007-03-01

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[unnzteenz]

hi thnx for the reply actually the issue is that i hv writtena certain peice of code :
***********************************************************
If fLastStmtDate1 Is Null Then
Let fLastStmtDate1 = Date(fStmtNextDate) - 1 Units Month;
End If;

Let fLastStmtDate2 = Date(fLastStmtDate1) + 1 Units Day;
***********************************************************
and in this part it is giving me the error -1267.. I am not gettign nay clue why this error is coming

 

[PHV]

I think the error is raised by the month line, not the day line.
I'd use a separate procedure for adding/subtracting months:

CREATE PROCEDURE AddMonth(dep DATE, nbm SMALLINT) RETURNING DATE;
  DEFINE res DATE; DEFINE nbj SMALLINT;
  LET nbj=0;
  WHILE 1=1
    ON EXCEPTION IN(-1267)
      LET nbj=nbj+1;
    END EXCEPTION
    LET res=DATE(dep-nbj UNITS DAY + nbm UNITS MONTH);
    EXIT WHILE;
  END WHILE
  RETURN res;
END PROCEDURE;

And now your actual code may look like this:

If fLastStmtDate1 Is Null Then
   Let fLastStmtDate1 = AddMonth(fStmtNextDate,-1);
End If;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886