Query returns the wrong Value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query returns the wrong Value

  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Query returns the wrong Value

    select distinct TO_CHAR(op_date, 'MM-dd-yyyy'), svc_id, count(op_id_data1)
    from tb_oper
    where (oper_id=3050 or (oper_id=3014 and svc_id not in (select soc_id from tb_soc)) or (oper_id=3015 and svc_id not in
    (select soc_id from tb_soc)))
    and TO_CHAR(op_date, 'MM-dd-yyyy') >= '$P!{fromDate}'and TO_CHAR(op_date, 'MM-dd-yyyy') <= '$P!{toDate}'group by TO_CHAR(op_date, 'MM-dd-yyyy'), svc_id
    order by 1,2

    the from date and To date and values manually entered, but for example (06/01/2014 06/17/2014 , but i get data from 2013 also. please help.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Does '$P!{fromDate}' include the year component? I guessing that it does not. Also you should use between as in
    WHERE mydate between SYSDATE-14 and SYSDATE;
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,470

    Cool

    Quote Originally Posted by nightfly36 View Post
    select . . .
    . . . E t c . . .
    and TO_CHAR(op_date, 'MM-dd-yyyy') >= '$P!{fromDate}'and TO_CHAR(op_date, 'MM-dd-yyyy') <= '$P!{toDate}'group by TO_CHAR(op_date, 'MM-dd-yyyy'), svc_id

    . . . please help.
    1) You should always compare DATES to DATES, do not convert to character
    2) This: "TO_CHAR(op_date, 'MM-dd-yyyy') >= " will compare the month first, day next and finally the year. Therefore '06/17/2013' will be greater than '06/01/2014'


    Don't know what "$P!{fromDate}" means, but if is a character representation of a date, you should modify your query to something like this:
    Code:
    SELECT . . . 
    . . .  E t c  . . .
      AND op_date >= TO_DATE('$P!{fromDate}','MM/DD/YYYY') 
        AND op_date <= TO_DATE('$P!{fromDate}','MM/DD/YYYY')
    GROUP BY op_date, svc_id
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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