Determine the 15th of the following month

Posted on 2013-09-17
Medium Priority
Last Modified: 2013-09-17
I need to determine the 15th of the following month in an Oracle Select statement based on a date field.

select sysdate             "MY DATE",
           add-code-here "THE 15TH"
from   dual

sysdate is substituted here in place of a database field.

This code works except for when the selected date falls in December

select to_date(
         -- compute year
         to_char(sysdate,'yyyy') ||
         -- compute month
         substr(to_char('00' || (to_char(sysdate,'mm') + 1)),-2,2) ||
         -- add day
         'yyyymmdd') "CAL DATE"
from dual
Question by:BigCity52
  • 2
LVL 35

Accepted Solution

johnsone earned 2000 total points
ID: 39500403
add_months(trunc(sysdate, 'mm'),1 ) + 14
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39500406
Maybe something like:
select trunc(add_months(to_date('&mydate','MM/DD/YYYY'),1),'MM')+14 from dual;
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39500410
Another alternative:
select last_day(to_date('&mydate','MM/DD/YYYY'))+15 from dual;

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question