Home » SQL & PL/SQL » SQL & PL/SQL » Finding 15th of Prior Month
Finding 15th of Prior Month [message #244404] Tue, 12 June 2007 11:54 Go to next message
raahulv
Messages: 1
Registered: June 2007
Junior Member
Hi,
I have a requirement where I have to find 15th of prior month.

Ex: If the Current Date is 06/12/07 then the query must return
05/15/07.

Thanks,
RV
Re: Finding 15th of Prior Month [message #244406 is a reply to message #244404] Tue, 12 June 2007 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    data as (
  3      select to_date('02/01/2007','MM/DD/YYYY') d from dual
  4      union all
  5      select to_date('02/14/2007','MM/DD/YYYY') d from dual
  6      union all
  7      select to_date('02/15/2007','MM/DD/YYYY') d from dual
  8      union all
  9      select to_date('02/16/2007','MM/DD/YYYY') d from dual
 10      union all
 11      select to_date('02/28/2007','MM/DD/YYYY') d from dual
 12      union all
 13      select to_date('06/01/2007','MM/DD/YYYY') d from dual
 14      union all
 15      select to_date('06/14/2007','MM/DD/YYYY') d from dual
 16      union all
 17      select to_date('06/15/2007','MM/DD/YYYY') d from dual
 18      union all
 19      select to_date('06/16/2007','MM/DD/YYYY') d from dual
 20      union all
 21      select to_date('06/30/2007','MM/DD/YYYY') d from dual
 22      union all
 23      select to_date('07/01/2007','MM/DD/YYYY') d from dual
 24      union all
 25      select to_date('07/14/2007','MM/DD/YYYY') d from dual
 26      union all
 27      select to_date('07/15/2007','MM/DD/YYYY') d from dual
 28      union all
 29      select to_date('07/16/2007','MM/DD/YYYY') d from dual
 30      union all
 31      select to_date('07/31/2007','MM/DD/YYYY') d from dual
 32    )
 33  select d, 
 34         case 
 35         when extract(day from d) <= 15 
 36           then trunc(add_months(d,-1),'MON')+14
 37         else trunc(d,'MON')+14 end d2
 38  from data
 39  /
D           D2
----------- -----------
01-FEB-2007 15-JAN-2007
14-FEB-2007 15-JAN-2007
15-FEB-2007 15-JAN-2007
16-FEB-2007 15-FEB-2007
28-FEB-2007 15-FEB-2007
01-JUN-2007 15-MAY-2007
14-JUN-2007 15-MAY-2007
15-JUN-2007 15-MAY-2007
16-JUN-2007 15-JUN-2007
30-JUN-2007 15-JUN-2007
01-JUL-2007 15-JUN-2007
14-JUL-2007 15-JUN-2007
15-JUL-2007 15-JUN-2007
16-JUL-2007 15-JUL-2007
31-JUL-2007 15-JUL-2007

15 rows selected.

Regards
Michel
Re: Finding 15th of Prior Month [message #244419 is a reply to message #244406] Tue, 12 June 2007 13:44 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
It seems to me that the OP was just asking for
with
    data as (
      select to_date('02/01/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('02/14/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('02/15/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('02/16/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('02/28/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('06/01/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('06/14/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('06/15/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('06/16/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('06/30/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('07/01/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('07/14/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('07/15/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('07/16/2007','MM/DD/YYYY') d from dual
      union all
      select to_date('07/31/2007','MM/DD/YYYY') d from dual
    )
  select d, 
         trunc(add_months(d,-1), 'MON') + 14
  from data
Re: Finding 15th of Prior Month [message #244420 is a reply to message #244419] Tue, 12 June 2007 13:55 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understood the previous 15.
Too simple problem for my understanding. Smile

Regards
Michel
Previous Topic: Functions and exceptions
Next Topic: Hierarchical SQL problem
Goto Forum:
  


Current Time: Wed Dec 07 09:06:13 CST 2016

Total time taken to generate the page: 0.14783 seconds