5 Replies Latest reply: Sep 27, 2013 4:27 PM by Mark D Powell RSS

    Date Issue

    927578

      hI

       

       

      when i update date column - '29-MAR-2013 12:15:10'  it's storing as '29-MAR-2013 12:15:10'. but when i store '31-MAR-2013 12:15:10' it's storing as '31-MAR-2013 12:15:10 AM' with 'AM' . how this happening ?

        • 1. Re: Date Issue
          Hoek
          • 2. Re: Date Issue
            927578

            but i didn't change any settings    

            • 3. Re: Date Issue
              Hoek

              You have to provide some more input.

              Post your actual statements, your database version, your nls setttings and the IDE/Tools (SQL*Plus/PL/SQL Developer/...?)  you're working with.

              A date format can be influenced in many ways.

              • 4. Re: Date Issue
                EdStevens

                927578 wrote:

                 

                hI

                 

                 

                when i update date column - '29-MAR-2013 12:15:10'  it's storing as '29-MAR-2013 12:15:10'.

                No it isn't.  At least, not if the column is defined (as it should be) as DATE.

                It is stored in oracle's internal, binary format.

                 

                 

                but when i store '31-MAR-2013 12:15:10' it's storing as '31-MAR-2013 12:15:10 AM' with 'AM' .

                 

                No it isn't.  At least, not if the column is defined (as it should be) as DATE.

                It is stored in oracle's internal, binary format.

                 

                 

                how this happening ?

                You are compering apples to oranges when it comes to handling your NLS_DATE_FORMAT settings when you query the data.

                 

                Beyond that, it's impossible to say with no more information you've given.  Go through the link Hoek gave you.

                • 5. Re: Date Issue
                  Mark D Powell

                  927578, any update?  You can control how a date appears at the SQL statement level using the to_char function as in

                   

                  > c /to_char/sysdate, to_char/

                   

                  1* select sysdate, to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM') FORMATTED from sys.dual

                   

                  > /

                   

                  SYSDATE FORMATTED

                   

                  --------- ----------------------

                   

                  27-SEP-13 2013-09-27 12:26:28 PM

                   

                  >

                  - -

                  HTH -- Mark D Powell --