Comparing SQL Oracle DATE returns incorrect result

Question

I have a REPORTDATE column in a database ( DATETIME ). I want to extract only the DATE from DATETIME, then do a COUNT for each day and put a WHERE to limit only dates after a certain date.

So, I have this item:

 SELECT to_char(REPORTDATE, 'DD.MM.YYYY') AS MY, COUNT(*) from INCIDENT where to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013' GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY') 

It returns me the results, but I can notice the wrong result, for example: 30.10.2013 , which is the wrong result.

How to solve this?

Answer 1

WHERE to_char (REPORTDATE, 'DD.MM.YYYY')> '09 .11.2013 '

You are comparing two STRINGS . You need to compare DATE . As I said in another answer here, you need to leave the date the same as for DATE calculations. TO_CHAR is for display, and TO_DATE is for converting a string literal to DATE.

 SELECT TO_CHAR(REPORTDATE, 'DD.MM.YYYY'), COUNT(*) FROM TABLE WHERE REPORTDATE > TO_DATE('09.11.2013', 'DD.MM.YYYY') GROUP BY TO_CHAR(REPORTDATE, 'DD.MM.YYYY') 

In addition, REPORTDATE is a DATE column, so it will have a datetime element. So, if you want to exclude the time element when comparing, you need to use TRUNC

 WHERE TRUNC(REPORTDATE) > TO_DATE('09.11.2013', 'DD.MM.YYYY') 

However, when using TRUNC in a column any regular index in this column will be suppressed. In terms of performance, it is better to use the date range condition .

For instance,

 WHERE REPORTDATE BETWEEN TO_DATE('09.11.2013', 'DD.MM.YYYY') AND TO_DATE('09.11.2013', 'DD.MM.YYYY') +1 

Answer 2

The to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013' condition to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013' compared with strings, so 30.10.2013 after 09.11.2013 . You need to compare dates, not string values, so you need to change your query to the following.

 SELECT to_char(REPORTDATE, 'DD.MM.YYYY') AS MY, COUNT(1) from INCIDENT where trunc(REPORTDATE)> to_date('09.11.2013', 'DD.MM.YYYY') GROUP BY to_char(REPORTDATE, 'DD.MM.YYYY') 

Note. I added a small modification from count(*) to count(1) to optimize the query with the same results.