5 Replies Latest reply: Sep 27, 2013 11:27 AM 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 ?

        • 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 --