how to select 1st and 15th of every month
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to select 1st and 15th of every month

  1. #1
    Join Date
    Jun 2008
    Posts
    3

    how to select 1st and 15th of every month

    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

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    How about this:

    1 with dnum as
    2 (select add_months(trunc(sysdate,'YY'),9) - trunc(sysdate) days
    3 from dual),
    4 data as
    5 (select trunc(sysdate)+level-1 day
    6 from dnum
    7 connect by level <= days)
    8 select day
    9 from data
    10 where day in (trunc(day,'MM'),trunc(day,'MM')+14)
    11* order by 1
    10:54:49 sspopov@TDSDEV> /

    DAY
    --------------------
    15-JUN-2008 00:00:00
    01-JUL-2008 00:00:00
    15-JUL-2008 00:00:00
    01-AUG-2008 00:00:00
    15-AUG-2008 00:00:00
    01-SEP-2008 00:00:00
    15-SEP-2008 00:00:00

    7 rows selected.

    Sergey

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    Code:
    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.

  4. #4
    Join Date
    Jun 2008
    Posts
    3
    Thank you all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width