1 2 Previous Next 17 Replies Latest reply on Dec 10, 2012 5:12 PM by rp0428 Go to original post
      • 15. Re: Strange dates/times in the DB
        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
          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 database, it's possible that you're hitting that error.

          1 person found this helpful
          • 17. Re: Strange dates/times in the DB
            . . .

            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 person found this helpful
            1 2 Previous Next