This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 10, 2012 9:12 AM by rp0428 Go to original post RSS
  • 15. Re: Strange dates/times in the DB
    978688 Newbie
    Currently Being Moderated
    The SYSDATE and the OS date are both fine. This happened on 50 or so records in this table a couple of months ago (October). The data comes over from another system. I guess what we are really trying to determine is how Oracle allowed dates like this to be inserted in the first place. If the data on the other system was like this, wouldn't Oracle scream the instant the insert was attempted? Is it a bug of some sort?
  • 16. Re: Strange dates/times in the DB
    Justin Cave Oracle ACE
    Currently Being Moderated
    In old versions of OCI, it was possible to create a buggy C application that created a time.h tm structure (used to represent a date) representing an invalid date and to insert that into the database bypassing the date validation code. So far as I'm aware, patches for all those bugs have been available for many, many years. But if you have a buggy application running on an old, unpatched Oracle client that is, apparently, hitting an unpatched 10.2.0.1 database, it's possible that you're hitting that error.

    Justin
  • 17. Re: Strange dates/times in the DB
    rp0428 Guru
    Currently Being Moderated
    >
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH:MI:SS AM';
    . . .
    SELECT CURE_DT, DUMP(CURE_DT) FROM RT_TIRE WHERE BARCODE = '61790283';

    CURE_DT DUMP(CURE_DT)
    --------------------------------------------------------
    22-OCT-12 15:22:30 PM
    Typ=12 Len=7: 120,112,10,22,28,23,31
    >
    If that output was after you set the time to 12-hour format then the hour value is 12 hours too high; meaning the time was actually stored in 24-hour format.

    Since this is the value you originally posted
    >
    Somehow the following DATE field data (this is from a SQL*Plus query that did not manipulate the date just did a straight-up query, so the format is our default) is in our database:

    2012/10/22 27:22:30
    >
    I have seen that happen in 10g when data from one instance was imported (imp) from another instance where the patch versions of the DB was different.

    Since you can detect the values that are in error you should be able to find them in the table and replace them with the correct values using a script. The values in any index that uses that column should get updated automatically.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points