suraha suraha - 6 months ago 7
SQL Question

Why am I getting different results when comparing dates two ways?

This query:

select count(*), trim(data_date)
from man
where data_status = 'received' and data_date > sysdate-7
group by trim(data_date);


gives result like:

199 05-APR-16


But this query:

select count(*), trim(data_date)
from man
where data_status = 'received' and trunc(data_date) = date '2016-04-05'
group by trim(data_date);


gives results like:

347 05-APR-16


Why are the queries giving different results for the same day?

Answer

Because your man_date_sub values are not all at midnight. If you keep running the first query the number of records returned will (probably) gradually reduce. That is only happening to the count for the 5th as that is a week ago. Your sysdate - 7 is a moving target, not just as you move from day to day, but as time passes during the day.

You can check the times with:

select to_char(man_date_sub, 'YYYY-MM-DD HH24:MI:SS'),
  to_char(sysdate - 7, 'YYYY-MM-DD HH24:MI:SS'),
  man_date_sub - (sysdate - 7)
from man
where trunc(man_date_sub) = date '2016-04-05';

You'll see that some have times before the current sysdate time, while others have times after it. The third, generated, column will show some positive and some negative values.

In your second query you're comparing trunc(man_date_sub), which sets the time part to midnight, with date '2016-04-05', which is also at midnight; so all the records at any time on that day now match.

You can go back to midnight on your 7-day range, and get an equivalent result, by truncating sysdate:

select count(*), trim(man_date_sub) 
from man
where man_status = 'SUBMITTED' and man_date_sub > trunc(sysdate)-7
group by trim(man_date_sub);

Your use of the trim() function is a bit odd; all you're doing is removing leading and trailing whitespace from the string '05-APR-16', which isn't actually doing anything. You're also relying on implcit conversion of the date to a string using your session NLS_DATE_FORMAT. It would be better to specify the format:

select count(*), to_char(man_date_sub, 'YYYY-MM-DD')
from man
where man_status = 'SUBMITTED' and man_date_sub > trunc(sysdate)-7
group by to_char(man_date_sub, 'YYYY-MM-DD');

If you ran your original query in a session that had an NLS_DATE_FORMAT that included time elements then you wouldn't get the result you expect.

I'm not sure if you're confusing it with trunc(), though clearly you're using that elsewhere. Truncating a date sets the time portion to midnight (by default; it can do other things), but leaves it as a date, which would be suitable for grouping but should still be formatted explicitly for display.

Comments