2

I have REPORTDATE column in database (DATETIME) type. I want to extract only DATE value from the DATETIME, then to do COUNT for each day and to put WHERE clause to restrict only dates later than some specific date.

So I have this clause:

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 results but but I can notice wrong result such as : 30.10.2013 which is wrong result.

How to solve this?

Veljko
  • 1,708
  • 12
  • 40
  • 80

2 Answers2

8

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

You are comparing two STRINGS. You need to compare the DATEs. As I already said in the other answer here, you need to leave the date as it is for DATE calculations. TO_CHAR is for display, and TO_DATE is to convert a string literal into 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')

Also, REPORTDATE is a DATE column, hence it will have datetime element. So, if you want to exclude the time element while comparing, you need to use TRUNC

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

However, applying TRUNC on the date column would suppress any regular index on that column. From performance point of view, better use a Date range condition.

For example,

WHERE REPORTDATE
BETWEEN 
        TO_DATE('09.11.2013', 'DD.MM.YYYY')
AND     
        TO_DATE('09.11.2013', 'DD.MM.YYYY') +1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    @Dejan, You need ot put the TRUNC over the column as I have shown in the end. – Lalit Kumar B Mar 12 '15 at 09:02
  • @Lalit Kumar B - Regarding this part of your answer: "You are comparing two STRINGS. You need to compare the DATEs": I think it's worth pointing out that the reason the OP's original query didn't work is *not* due to the use of string comparison instead of date comparison. It's due to the *way* they did the string comparison. If they had used to_char(REPORTDATE, 'YYYY.MM.DD')>'2013.09.11', then the query would have worked. – dcp Mar 12 '15 at 12:26
  • @dcp, it is like saying `'a' > 'b'` – Lalit Kumar B Mar 12 '15 at 13:39
  • @Latif Kumar B - Not sure I understand your comment. 'a' is actually less than 'b'. My point was that the query wasn't wrong because he was comparing strings instead of dates, it was how he was comparing the strings that was incorrect. However, your answer seems to indicate that you must use date comparisons to get the query to work correctly, which is incorrect. – dcp Mar 12 '15 at 14:12
  • Seems like you did not understand what I said. It might just get working for your specific NLS_* settings, it may not work on other clients or servers. That is why an explicit date format is important. It is always, I will repeat always important to explicitly transform your string into a date using the built-in to_date function. Have a look at this similar question stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql – Lalit Kumar B Mar 12 '15 at 15:13
  • `Also, REPORTDATE is a DATE column, hence it will have datetime element. So, if you want to exclude the time element while comparing, you need to use TRUNC` That is not a good idea unless there is a function-based index on the column. There are better ways of doing this without resorting to `TRUNC()`. – David Faber Mar 12 '15 at 16:31
  • @Latitu Kumar B - NLS_DATE_FORMAT doesn't come into play here, because the mask passed to to_char is YYYY.MM.DD, which uses all numbers. So unless I'm missing something, to_char(REPORTDATE, 'YYYY.MM.DD') will always produce the same output regardless of any NLS settings. If you have an example where that would not be the case, then feel free to post it. I do agree with you that it's best to compare dates to dates when possible. – dcp Mar 12 '15 at 17:23
  • @DavidFaber I agree regarding functuon-based index here. A regular index would be ignored if a function is applied. But that is not the main answer to the question. I just mentioned it to OP since he is comparing dates, and with only date element. I can't go deeper into explaining about function-based index in the answer, since it would be out if scope of the current question. – Lalit Kumar B Mar 12 '15 at 17:36
  • 1
    @DavidFaber I added that part now. – Lalit Kumar B May 29 '15 at 06:35
0

The condition to_char(REPORTDATE, 'DD.MM.YYYY')>'09.11.2013' compare to strings, so 30.10.2013 is after 09.11.2013. You need to compare the dates, not the string values, so you have 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 little modification from count(*) to count(1) for optimize the query having the same results.

Davide Lorenzo MARINO
  • 26,420
  • 4
  • 39
  • 56
  • `COUNT(1)` doesn't offer anything over `COUNT(*)`. – David Faber Mar 12 '15 at 16:31
  • COUNT(*) check if any of the columns has a value not null. COUNT(1) doesn't check single columns. It return the number of rows, not the number of rows with at least one field not null. – Davide Lorenzo MARINO Mar 12 '15 at 16:42
  • `COUNT(*) check if any of the columns has a value not null` - What is the basis for this statement? Please see here: http://stackoverflow.com/questions/1221559/count-vs-count1 – David Faber Mar 12 '15 at 17:17
  • @DavideLorenzoMARINO Count(*) and Count(1) are the same in all aspects. Unless you are on primitive release where it used to be some difference. There has had been numerous discussions on this in the past. Just google it and see. The best answer by the master Thomas Kyte here https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1156159920245 – Lalit Kumar B May 29 '15 at 06:38