unbelieveble!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: unbelieveble!!

  1. #1
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    unbelieveble!!

    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.

  2. #2
    Join Date
    Feb 2001
    Posts
    295
    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"
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    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.

  6. #6
    Join Date
    Feb 2001
    Posts
    295
    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.
    Last edited by adrianomp; 07-07-2003 at 06:36 PM.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  7. #7
    Join Date
    Mar 2002
    Posts
    534

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

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

    HTH
    Mike
    Last edited by mike9; 07-07-2003 at 06:51 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width