Home » SQL & PL/SQL » SQL & PL/SQL » how to select 1st and 15th of every month (oracle 10g)
how to select 1st and 15th of every month [message #326833] Thu, 12 June 2008 15:41 Go to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member
hello,

what is the select statement to get 1st and 15th of every month from todays date till the end of current fiscal year (sep 30th)

ex:
June 15th - 06/15/2008
July 1st - 07/01/2008
July 15th - 07/15/2008
-
-
sep 1st - 09/01/2008
sep 15th - 09/15/2008
Re: how to select 1st and 15th of every month [message #326835 is a reply to message #326833] Thu, 12 June 2008 15:57 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
SQL> set term on echo on time on
13:56:20 SQL> @dates
13:56:23 SQL> select '06/15/2008' from dual
13:56:23   2  union
13:56:23   3  select '07/01/2008' from dual
13:56:23   4  union
13:56:23   5  select '07/15/2008' from dual
13:56:23   6  union
13:56:23   7  select '08/01/2008' from dual
13:56:23   8  union
13:56:23   9  select '08/15/2008' from dual
13:56:23  10  union
13:56:23  11  select '09/01/2008' from dual
13:56:23  12  union
13:56:23  13  select '09/15/2008' from dual
13:56:23  14  /

'06/15/200
----------
06/15/2008
07/01/2008
07/15/2008
08/01/2008
08/15/2008
09/01/2008
09/15/2008

7 rows selected
Re: how to select 1st and 15th of every month [message #326837 is a reply to message #326835] Thu, 12 June 2008 16:25 Go to previous messageGo to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member

Smile i know this,
i want a select statement without hard coding any dates, it should be based on sysdate
Re: how to select 1st and 15th of every month [message #326850 is a reply to message #326837] Thu, 12 June 2008 18:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT days
  2  FROM   (SELECT SYSDATE + ROWNUM AS days
  3  	     FROM   DUAL
  4  	     CONNECT BY LEVEL <= TO_DATE ('30-SEP-2008', 'DD-MON-YYYY') - SYSDATE)
  5  WHERE  TO_CHAR (days, 'dd') IN (1, 15)
  6  /

DAYS
-----------
15-JUN-2008
01-JUL-2008
15-JUL-2008
01-AUG-2008
15-AUG-2008
01-SEP-2008
15-SEP-2008

7 rows selected.

SCOTT@orcl_11g> 


Re: how to select 1st and 15th of every month [message #326870 is a reply to message #326833] Thu, 12 June 2008 22:27 Go to previous messageGo to next message
cnvegnix
Messages: 21
Registered: June 2008
Junior Member
with a as (
select 1 d
  from dual
union
select 15 d from dual
),
b as (
select extract(month from ym) m, extract(year from ym) y
  from (select add_months(sysdate, rownum - 1) ym
          from dual
        connect by level <=
                   months_between(to_date('30/09/2008', 'dd/mm/yyyy'),
                                  sysdate) + 1)
)
select days
  from (select to_date(a.d || '/' || b.m || '/' || b.y, 'dd/mm/yyyy') days
          from a, b)
 where days > sysdate
 order by days;
Re: how to select 1st and 15th of every month [message #326896 is a reply to message #326833] Fri, 13 June 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    lines as ( 
  3      select level-1 line 
  4      from dual 
  5      connect by level <= (to_date('30/09/2008','DD/MM/YYYY') - trunc(sysdate,'month')) / 15 
  6    )
  7  select add_months(trunc(sysdate,'month'),trunc(line/2)) + 14 * mod(line,2) days
  8  from lines
  9  /
DAYS
----------
01/06/2008
15/06/2008
01/07/2008
15/07/2008
01/08/2008
15/08/2008
01/09/2008
15/09/2008

8 rows selected.

Regards
Michel

[Updated on: Fri, 13 June 2008 00:43]

Report message to a moderator

Re: how to select 1st and 15th of every month [message #327037 is a reply to message #326896] Fri, 13 June 2008 09:07 Go to previous messageGo to next message
vinodkumarn
Messages: 54
Registered: March 2005
Member
thank you all
Re: how to select 1st and 15th of every month [message #327061 is a reply to message #327037] Fri, 13 June 2008 10:22 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select to_char(days, 'fmMon ddth'||' - '||'fmmm/dd/yyyy') days
  2  from
  3     (select sysdate + level as days
  4      from dual
  5      connect by level <= to_date ('09/30/2008', 'mm/dd/yyyy') - sysdate)
  6  where extract(day from days) in (1, 15)
  7  /

DAYS
---------------------
Jun 15th - 06/15/2008
Jul 1st - 07/01/2008
Jul 15th - 07/15/2008
Aug 1st - 08/01/2008
Aug 15th - 08/15/2008
Sep 1st - 09/01/2008
Sep 15th - 09/15/2008

7 rows selected.
Previous Topic: Tablespace monitoring (merged)
Next Topic: Help in Row to Col
Goto Forum:
  


Current Time: Fri Dec 02 12:08:07 CST 2016

Total time taken to generate the page: 0.08925 seconds