1 2 Previous Next 17 Replies Latest reply on Dec 10, 2012 5:12 PM by rp0428

    Strange dates/times in the DB

    DKM
    DKM Dec 7, 2012 8:39 PM
      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*

      How did it get in there with hours like that? I have tried a few different insert statements and I can't find a way to get a date like that into the database. There is no date arithmetic done on it on this server, it's probably coming from another machine/server just like that (still trying to track that down). Does SQL*Loader or some other method bypass Oracle's date validation or is there something else I'm missing?

      It's an Oracle DB (10.2.0.1.0) and it is just a standard DATE field.
        • 1. Re: Strange dates/times in the DB
          sb92075
          sb92075 Dec 7, 2012 8:41 PM (in response to DKM)
          975685 wrote:
          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*

          How did it get in there with hours like that? I have tried a few different insert statements and I can't find a way to get a date like that into the database. There is no date arithmetic done on it on this server, it's probably coming from another machine/server just like that (still trying to track that down). Does SQL*Loader or some other method bypass Oracle's date validation or is there something else I'm missing?

          It's an Oracle DB (10.2.0.1.0) and it is just a standard DATE field.
          I don't believe you.


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Strange dates/times in the DB
            cmsneed
            cmsneed Dec 7, 2012 8:53 PM (in response to DKM)
            Is there a script that does an ALTER SESSION SET NLS_DATE_FORMAT when you log on?

            SQL> alter session set NLS_DATE_FORMAT='YYYY/MM/DD MI:HH24:SS';

            Session altered.

            SQL> select sysdate from dual;

            SYSDATE
            -------------------
            2012/12/07 51:15:19
            • 3. Re: Strange dates/times in the DB
              DKM
              DKM Dec 7, 2012 9:03 PM (in response to cmsneed)
              No, no 'alter session' command. Easily repeatable by other people in my group. Even better, if you run the same query in SQL*Developer the same date comes up as:

              *23-OCT-12 03.22.30*

              The default NLS Format for DATE on my SQL*Developer is:

              DD-MON-RR HH24.MI.SS

              The default NLS Format for DATE on that server is:

              DD-MON-RR HH24.MI.SS

              How 'bout that?!
              • 4. Re: Strange dates/times in the DB
                Vijayaraghavan Krishnan
                Vijayaraghavan Krishnan Dec 7, 2012 9:07 PM (in response to DKM)
                HI,

                Try setting up the NLS_DATE_FORMAT for the session in sql*plus and let us know the output.
                Alter session set NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS'
                Thanks,
                Vijay
                • 5. Re: Strange dates/times in the DB
                  DKM
                  DKM Dec 7, 2012 9:11 PM (in response to Vijayaraghavan Krishnan)
                  Did the alter session and the date comes back as:

                  *22-OCT-12 27:22:30*
                  • 6. Re: Strange dates/times in the DB
                    Vijayaraghavan Krishnan
                    Vijayaraghavan Krishnan Dec 7, 2012 9:14 PM (in response to DKM)
                    Sorry I want to try different and gave you the different format
                    Alter session set NLS_DATE_FORMAT = 'DD-MON-YY HH:MI:SS AM'
                    Thanks,
                    Vijay
                    • 7. Re: Strange dates/times in the DB
                      DKM
                      DKM Dec 7, 2012 9:18 PM (in response to Vijayaraghavan Krishnan)
                      Altered the session as stated and the date comes up as:

                      *22-OCT-12 15:22:30 PM*
                      • 8. Re: Strange dates/times in the DB
                        rp0428
                        rp0428 Dec 7, 2012 9:20 PM (in response to DKM)
                        >
                        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

                        How did it get in there with hours like that? I have tried a few different insert statements and I can't find a way to get a date like that into the database. There is no date arithmetic done on it on this server, it's probably coming from another machine/server just like that (still trying to track that down). Does SQL*Loader or some other method bypass Oracle's date validation or is there something else I'm missing?

                        It's an Oracle DB (10.2.0.1.0) and it is just a standard DATE field.
                        >
                        Well I've never known the DUMP function to lie about what it finds so show us that result 
                        SELECT dateColumn, DUMP(dateColumn) from myTable;
                        • 9. Re: Strange dates/times in the DB
                          Mark Malakanov (user11181920)
                          Mark Malakanov (user11181920) Dec 7, 2012 9:21 PM (in response to DKM)
                          show us entirely all sql statements you executed. not just result.
                          • 10. Re: Strange dates/times in the DB
                            Vijayaraghavan Krishnan
                            Vijayaraghavan Krishnan Dec 7, 2012 9:27 PM (in response to DKM)
                            HI,

                            Is this a SYSDATE OUTPUT? Please post the OS

                            Thanks,
                            Vijay

                            Edited by: Vijayaraghavan Krishnan on Dec 7, 2012 3:27 PM
                            • 11. Re: Strange dates/times in the DB
                              Osama_Mustafa
                              Osama_Mustafa Dec 9, 2012 5:52 AM (in response to DKM)
                              The db gets its time from the OS So Post Date Command in your OS level
                              • 12. Re: Strange dates/times in the DB
                                DKM
                                DKM Dec 10, 2012 2:18 PM (in response to Vijayaraghavan Krishnan)
                                This is not SYSDATE output. The sequence of queries and alters as per your requests were:

                                SELECT CURE_DT FROM RT_TIRE WHERE BARCODE = '61790283';

                                CURE_DT
                                -------------------
                                2012/10/22 27:22:30

                                ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

                                Session altered.

                                SELECT CURE_DT FROM RT_TIRE WHERE BARCODE = '61790283';

                                CURE_DT
                                -------------------
                                22-OCT-12 27:22:30

                                ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH:MI:SS AM';

                                Session altered.

                                SELECT CURE_DT FROM RT_TIRE WHERE BARCODE = '61790283';

                                CURE_DT
                                -------------------
                                22-OCT-12 15:22:30 PM

                                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
                                • 13. Re: Strange dates/times in the DB
                                  Mark Malakanov (user11181920)
                                  Mark Malakanov (user11181920) Dec 10, 2012 3:36 PM (in response to DKM)
                                  22-OCT-12 27:22:30
                                  22-OCT-12 15:22:30 PM
                                  It looks like the value is corrupted. It may be corrupted in 2 places - in table and in index(exes) that have CURE_DT.

                                  find index from plan of
                                  SELECT CURE_DT FROM RT_TIRE WHERE BARCODE = '61790283';

                                  and try to rebuild it if it contains CURE_DT.
                                  1 person found this helpful
                                  • 14. Re: Strange dates/times in the DB
                                    Vijayaraghavan Krishnan
                                    Vijayaraghavan Krishnan Dec 10, 2012 4:29 PM (in response to DKM)
                                    Hi,

                                    Post us the sysdate output and as well the OS date.

                                    Is your output from sysdate is displaying properly? Even though if your record got corrupted too the Oracle should will throw the error when it returns, insert the date if it is not properly entered.

                                    In your case it is returning 22-OCT-12 27:22:30. Oracle should throw the error if it is the date datatype.

                                    Thanks,
                                    Vijay

                                    Edited by: Vijayaraghavan Krishnan on Dec 10, 2012 10:28 AM
                                    1 2 Previous Next