CODEWITHSUNDEEP
sqloracle-database
mfgn
mfgn

Reputation: 100

Adding a month to last month of the year logic

A reporting period is manually entered when a report is running. I've set up the parameters as such: 

AND (TRUNC(COJ.DATE_ENTERED) >= TO_DATE('&DATE_FROM', 'MM/YYYY') OR '&DATE_FROM' IS NULL)
AND (TRUNC(COJ.DATE_ENTERED) <= TO_DATE('&DATE_TO', 'MM/YYYY') OR '&DATE_TO' IS NULL)

These dates will be entered as 01/2015 and 12/2019 for example. Then returns data between these dates. I need however to add the logic that if its the last month of the year it then adds a month and goes to the next year. Example- 12/2019 would be changed to 01/2020. Can anyone help?

DATE_ENTERED is the data associated with each record. Any questions just ask,

Upvotes: 0

Views: 197

Answers (2)

user5683823
user5683823

Reputation:

If the dates are entered as '01/2015' and '08/2017' for example, and if all the data for August 2017 must be INCLUDED in the report, then the where clause should include

...
and (COJ.DATE_ENTERED >= TO_DATE('&DATE_FROM', 'MM/YYYY') OR '&DATE_FROM' IS NULL)
and (COJ.DATE_ENTERED <  ADD_MONTHS(TO_DATE('&DATE_TO', 'MM/YYYY'), 1) OR '&DATE_TO' IS NULL)

This should be regardless of "December" or whatnot. Also, there is no need to truncate DATE_ENTERED.

Upvotes: 1

kara
kara

Reputation: 3455

I don't see your problem. If you want to add a month to a date, you can use add_months(date, months):

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions004.htm#SQLRF00603

begin
    dbms_output.put_line('Sysdate: ' || sysdate);
    dbms_output.put_line('Sysdate: ' || trunc(sysdate, 'mm'));
    dbms_output.put_line('Sysdate: ' || add_months(sysdate, 12));
    dbms_output.put_line('Sysdate: ' || trunc(add_months(sysdate, 12), 'mm')); 
end;

Upvotes: 0

Related Questions