Rolling Current Month for Canned Reports

[njtornado]

I need to calculate the current month so I can include this in some canned reports we have.

What I need is all of the "current month", where it will automatically know how many days are in the month and if it's a leap year etc.? Also I don't want to have to update the date always...

We have been using "last 7 days" successfully in canned reports and we base this upon the sysdate so we don't have to always enter the current date into the report. So the current month would probably based upon sysdate or something similar.

Example: For this month (June 2002) the "current month" would include all days between June 1, 2002 12:00:00 AM - June 30, 2002 11:59:59 PM.

Is this possible?

 

[carp]

The SQL required is

SELECT trunc(sysdate,'MM'), last_day(sysdate) FROM dual;

If you are doing this report via PL/SQL, try something like this:

DECLARE
v_first_day DATE;
v_last_day DATE;
BEGIN
SELECT trunc(sysdate,'MM'), last_day(sysdate)
INTO v_first_day, v_last_day
FROM dual;
.
.
.
END;

This will give you the first and last day of the current month.

 

[njtornado]

Ok - it works!

Actually, we're using Business Objects as a reporting tool to access an Oracle 8.17 db so the final code is somewhat different. I've created an object in Business Objects with the following & it works like a charm:

@Select(Actual Ship Date) BETWEEN (trunc(sysdate,'MM')) AND (last_day(sysdate))

Thanks for your help!