31/06/2003 does not exist ...it's an invalid date.
SQL> select count(*)
2 from sales
3 where dat_sale = '31/06/2003';
where dat_venda = '31/06/2003'
*
ERROR at line 3:
ORA-01843: not a valid month
SQL> select dat_sale, count(*)
2 from sales
3 where dat_inc between to_date('30/06/2003', 'DD/MM/YYYY') AND to_date('01/07/2003', 'DD/MM/YYYY')
4 group by dat_sale
5 /
Are you sure you paste that code without editing? Anyway, what are the data types involved?
SQL> select dat_sale, count(*)
2 from sales
3 where dat_inc between to_date('30/06/2003', 'DD/MM/YYYY') AND to_date('01/07/2003', 'DD/MM/YYYY')
4 group by dat_sale
5 /
I bet dat_sale (or dat_venda?) is not a date column, but a varchar one containing only the day (otherwise, you probably wouldn't be able to group by that column).
Further, the "between" comparison involved dat_inc, but the results show dat_sales. Probably someone inserted the value "31-JUN-03" manually, your last query didn't return anything because you're comparing strings, and being strings "31-JUN-03" <> "31/06/2003"
An ounce of action is worth a ton of theory.
—Friedrich Engels
Sorry, it's dat_sale... dat_venda appears because I changed manually, I dont want you to know my table models.... whatever, it's impossible to insert 31/06/2003 in oracle, and there it is. And dat_saleb (dat_venda) is a DATE datatype.
Given how critical date functionality is to many users of Oracle, and given how long date function/formatting has been around, I seriously doubt it is a bug. You or someone has hosed the data in the table by allowing improper format to be used, or by not defining the table datatype correctly. What possible breach of security or opportunity for industrial espionage could there be by using the actual column name? Do you think anyone here could possibly care any less about what you name your columns, especially something as generic as date_sold, date_vend, etc? Don't tell me what the actual name is because I don't want you killing me after telling me.
SQL> select dat_sale, count(*)
2 from sales
3 where dat_inc between to_date('30/06/2003', 'DD/MM/YYYY') AND to_date('01/07/2003', 'DD/MM/YYYY')
4 group by dat_sale
5 /
... would be probably show something like this, given you are grouping by dates, unless you truncate your dates or inserts them once a day in a single routine. Is that the case? That's why I suspected it's not a date column.
Can you just type "desc sales"? We already know the table name and the column name, knowing the datatype won't hurt you so much.
that's probably simply something called "an application bug"
It would be nice if you would tell us how you entered this date, because I'm sure that you didnt do it via sqlplus or anything like that.
Did you by any chance enter that value via OCI?
Did you by any chance use the OCI external datatype DATE?
If so, you should carefully read your Oracle Documentation and you would see that:
"When you enter a date in binary format by using the external datatype DATE, the database does not perform consistency or range checking. All data in this format must be validated before input."
Reply With Quote
that's probably simply something called "an application bug"
Bookmarks