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

    Unanswered: Returned rows are incorrect

    I have the following query that is supposed to return rows with startdate between the beginning of next month and 90 days from that date.

    The rows I want to return should have startdates from 9/1/06 to 11/01/06, but I'm getting rows where startdate = 10/1/05.

    Code:
    SELECT *   
    FROM dfutoskufcst c
    WHERE ITEM IN (SELECT DISTINCT a.item 
    	  	   		     FROM stsc.sku a, stsc.item b
    					 WHERE a.p_abc = 'A'
    					 AND b.p_stkclass IN ('010','010A')
    					 AND a.item = b.item)
    AND TO_CHAR(c.startdate,'MM/DD/YYYY') BETWEEN TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,0), 'MM/DD/YYYY')
    									  	  AND TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,2), 'MM/DD/YYYY')
    Last edited by ssmith001; 08-24-06 at 17:25.

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    How about
    Code:
    SELECT *   
    FROM   dfutoskufcst c
    WHERE  item IN
           ( SELECT DISTINCT a.item 
             FROM   stsc.sku a, stsc.item b
             WHERE  a.p_abc = 'A'
             AND    b.p_stkclass IN ('010','010A')
             AND    a.item = b.item )
    AND    c.startdate
           BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MM'), 1)
           AND ADD_MONTHS(TRUNC(SYSDATE,'MM'), 1) + 90

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    It's b/c you TO_CHAR'ed the date, and '10...' is between '09...' and '11...':

    Code:
    SQL> select TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,0), 'MMDDYYYY')
      2       , '10012005'
      3      , TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,2), 'MMDDYYYY') 
      4  from dual  
      5  where '10012005' between  TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,0), 'MMDDYYYY') and 
      6                            TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1,2), 'MMDDYYYY');
    
    TO_CHAR( '1001200 TO_CHAR(
    -------- -------- --------
    09012006 10012005 11012006
    Code:
    SQL> select ADD_MONTHS(LAST_DAY(SYSDATE) + 1,0),
      2         to_date('10/01/2005','MM/DD/YYYY'),
      3        ADD_MONTHS(LAST_DAY(SYSDATE) + 1,2) 
      4  from dual  
      5  where to_date('10/01/2005','MM/DD/YYYY') between  ADD_MONTHS(LAST_DAY(SYSDATE) + 1,0) and
      6                                                    ADD_MONTHS(LAST_DAY(SYSDATE) + 1,2);
    
    no rows selected
    -cf

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    Got it. Thanks guys!

Posting Permissions

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