DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014

    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
    Pittsburgh, PA
    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
    Lake Worth, FL


    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:
    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.