Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: Checking dates for month-end validation

    Hi,

    I have the following function:
    Code:
    CREATE FUNCTION isMonthEnd(inputDate DATE) RETURNING INT
    	DEFINE month_end_date DATE;
    	DEFINE return_val INT;
    	LET month_end_date = inputDate + 1 UNITS MONTH - DAY(inputDate) UNITS DAY;
    	IF (DATE(inputDate) == DATE(month_end_date)) THEN
    		LET return_val=1;
    	ELSE
    		LET return_val=0;
    	END IF;
    	RETURN return_val;
    END FUNCTION;
    It doesn't seem to be working... I mean - it always returns 0. In case, when the date is month-end already - it fails - saying the computation is out of range because adding a month makes it invalid if it is 31st day and the next month does not have 31 days.. Can anyone help me fix this?

    Apart from that, is there any easier way to get the month-end dates between given date range (start date to end date - both inclusive)? That would be great.

    Thank you in advance!

  2. #2
    Join Date
    Jan 2006
    Posts
    38
    Okay, I fixed it by changing the logic:
    Code:
    DROP FUNCTION isMonthEnd;
    CREATE FUNCTION isMonthEnd(inputDate DATE) RETURNING INT
    	DEFINE return_val INT;
    	DEFINE temp_date DATE;
    	LET temp_date = inputDate + 1 UNITS DAY;
    	IF (MONTH(temp_date) - MONTH (inputDate) > 0 ) THEN
    		LET return_val=1;
    	ELSE
    		LET return_val=0;
    	END IF;
    	RETURN return_val;
    END FUNCTION;
    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •