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.

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;

Quote:

---

Originally Posted by nightfly36

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

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

---

;)