Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Need help with a query involving dates

    I have the following query:

    Code:
    SELECT a.*
          , TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 1 ), 'MM' ) p_startdate
          , TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 1 ), 'MM' ) + 90 p_enddate  
          , b.p_dlrnet
          , b.p_dlrsplrcode
          , b.oh sku_oh
          , NVL(c.qty,0) srec_qty
          , p_pdc_spq
      FROM stsc.dfutoskufcst a
         , stsc.sku b
         , stsc.schedrcpts c
         , stsc.item d
    WHERE a.startdate BETWEEN TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 1 ), 'MM' )
    	  AND TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 1 ), 'MM' ) + 90
      AND a.skuloc = 'B400'
      AND a.item   = 'D1017-2173'
      AND a.skuloc = b.loc
      AND a.item   = b.item
      AND b.loc    = c.loc(+)
      AND b.item   = c.item(+)					 
      AND d.item   = b.item
    that returns values for p_startdate and p_enddate of 1/1/2007 and 4/1/2007 respectively. What I want to return is 1/1/2007 and 3/1/2007 which is next month + 90 days. The kicker however is that when I run it for a value of 45, I need it to return 1/1/2007 and 2/15/2007 which is basically a 45 day date range. Yes, I know that not all months have 30 days, but what the 90 days in this example is saying is "take the next 3 months" and a value of 45 says "take the next month and a half".

    As always, any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Instead of

    TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 1 ), 'MM' ) + 90


    WHy not use

    TRUNC( ADD_MONTHS( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 4 ), 'MM' )

    This will go up 4 months and then push it back to the first day in that month.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    No luck. When I use a value of 90, I need a date range from current month + 1 (1/1/2007 in this case) to current month + 3 (90/30 or 3/1/2007 in this case).

    When I run it with a value of 45 I need a date range from current month + 1 (1/1/2007 in this case) to current month + 1.5 (2/15/2007 in this case).

    Basically, if the mod(# of days, 30) = 0, use full months, if it's <> 0, use 1/2 months.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I dont understand why this is not as simple as getting rid of the ADD_MONTHS function in the upper bound of the BETWEEN operator.

  5. #5
    Join Date
    Sep 2004
    Posts
    60
    Try this :
    select add_months(Trunc( TO_DATE( '12-06-2006', 'mm-dd-yyyy' ), 'MM'), X) + Y from dual ;

    Where
    X = Number of full moths after Dec.
    Y = Number of days-1

    Ex.
    if you want Jan 1, X=1, Y=0
    if you want feb 15, X=2 Y=14
    if you want Mar 1, X=3 Y=0

    Your Query was based on logic of having 30 days in each month, which would fail. It was simply adding days to date coming out of function.

Posting Permissions

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