Audit Table query

[Vick]

hello,
         I am not very good with SQL. I have a simple question. I am trying to pull up number of records in the table before specific date. I get 2 different results from below 2 queries, I know the first one gives the wrong data but I would like to understand the root cause of it. Any help is appreciated.

1: select count(*) from COGIPF_ACTION where to_char(COGIPF_LOCALTIMESTAMP,'DD-MON-YY') < to_char(sysdate-10,'DD-MON-YY');

This gives count of 26696 which is wrong and when I view the column it shows me records of today and yesterday as well.


2: select count(*) from COGIPF_ACTION where to_char
(COGIPF_LOCALTIMESTAMP,'YYYY-MM-DD') < to_char(sysdate-10,'YYYY-MM-DD')

This one gives me correct data.

The only difference between 1 and 2 is 'DD-MON-YY' AGAINST 'YYYY-MM-DD'

Thanks,
Vick.

[goose]

Hi Vick

You dont specify you db but I'm guessing its oracle. Your problem is you are trying to use the less than (<) operator on character data by converting the date value to a string using to_char. While you can use less that operator on chars in your situation it wont work as Oracle cannot summize you want date based arithmatic. I dont know the date range thats in you data so I can only assume that the second sql works as you are crossing months/years that by chance give you the correct result.

Here is an example showing how the incorrect result is returned:

Code Select Expand

select
        case
                when to_date('28-JAN-2008') < to_date('01-FEB-2008')
                then 1
                else 0
        end
from dual
union all
select
        case
                when to_char(to_date('28-JAN-2008'),'DD-MON-YY') < to_char(to_date('01-FEB-2008'),'DD-MON-YY')
                then 1
                else 0
        end
from  dual


In general you want to avoid casting date values to characters unless its for display purposes only. A better oracle specific soltution for you situation is to use the trunc function which removes the time portion of the date. You could re-write you query like so:

Code Select Expand

select count(*)
from cogipf_action
where trunc(cogipf_localtimestamp)  < trunc(sysdate-10)


Good Luck
Angus