Results 1 to 5 of 5

Thread: days function

  1. #1
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    Question Unanswered: days function

    I need to list five consecutive days from some reference date. How to do it in a single query. It is like this.

    Ref date:12-03-2004 (mm-dd-yyyy)

    output to be:
    ------------
    12-03-2004
    12-04-2004
    12-05-2004
    12-06-2004
    12-07-2004

    Please help.
    regards,
    Chary

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Use any large table or view which will have enough rows for your needs (all_objects, user_objects etc). In this case 5 rows, so you've plenty of choice.

    Code:
    select to_date('12/03/2004','DD/MM/YYYY')+(rownum-1) 
    from all_objects
    where rownum <6
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    day functions

    yah it's working. thnx..
    but inmy case the reference date is not a fixed date, I'm getting it through subquery. Sometimes the reference date is not unique.. multiple dates (like date1, date2) will come. In this case query is giving only two records (date1 and date2).
    Is there any solution to get 10 dates as output (5 for date1, 5 for date2).

    thnx
    regards,
    Chary

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes you have lots of options, just form your query, subquery, joins etc as you wish ie....

    Code:
    DESC IBCST
    
      NAME           TYPE
      ============== =========== 
      ID             NUMBER (12) 
      COSTING_POINT  DATE 
      COSTING_START  DATE 
    
    select d1.costing_point+(rownum-1),
           d2.costing_start+(rownum-1)
    from   all_objects o,
           (select costing_point from ibcst where id = 644) d1,
           (select costing_start from ibcst where id = 2099) d2
    where  rownum < 6
    or...
    Code:
    select d1.costing_point+(rownum-1)
    from   all_objects o,
           (select costing_point from ibcst where id = 644) d1
    where  rownum < 6
    union all
    select d2.costing_start+(rownum-1)
    from   all_objects o,
           (select costing_start from ibcst where id = 2099) d2
    where  rownum < 6
    hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Mar 2004
    Location
    india
    Posts
    24

    days

    thnx bill it works... and serves my purpose
    regards,
    Chary

Posting Permissions

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