-
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;
-
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'
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|