Checking dates for month-end validation

exterminator · #1 (**permalink**) 07-31-06, 06:40

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!

exterminator · #2 (**permalink**) 07-31-06, 08:24

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech