unbelieveble!!

[Mnemonical]

Who can explain me this!!

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 /

DAT_VENDA COUNT(*)
--------- ----------
30-APR-03 18310
30-JUN-03 6366
31-JUN-03 175458

SQL> select count(*)
2 from sales
3 where to_char(dat_sale) = '31/06/2003';

COUNT(*)
----------
0


F.

[adrianomp]

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 /

DAT_VENDA COUNT(*)
--------- ----------
30-APR-03 18310
30-JUN-03 6366
31-JUN-03 175458

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"

[Mnemonical]

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.

So?

F.

[slimdave]

Originally posted by Mnemonical
Sorry, it's dat_sale... dat_venda appears because I changed manually, I dont want you to know my table models....

Wow. Must be pretty top secret, huh, that you can't post a table definition.

Firstly, stop relying on implicit conversion of dates -- use data format pictures every time.

Secondly, you have either hit a bug, or you are giving us wrong information.

[stecal]

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.

[adrianomp]

And dat_saleb (dat_venda) is a DATE datatype.

So, the following query...

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.

DAT_VENDA COUNT(*)
--------- ----------
30-APR-03 1
30-APR-03 1
30-APR-03 1
30-APR-03 1
.
.
.
30-JUN-03 1
30-JUN-03 1
30-JUN-03 1
.
.
.
31-JUN-03 1
31-JUN-03 1
31-JUN-03 1
.
.
.

What is the date_inc column, anyway?

Don't tell me what the actual name is because I don't want you killing me after telling me.

Steven, someday someone will kill you anyway, because of that constant bad mood.

[mike9]

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."

For more details have a look at:
http://otn.oracle.com/docs/products/...yp.htm#1003747

HTH
Mike