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.
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:
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:
select count(*)
from cogipf_action
where trunc(cogipf_localtimestamp) < trunc(sysdate-10)
Good Luck
Angus