This discussion is archived
5 Replies Latest reply: Sep 27, 2013 9:27 AM by Mark D Powell RSS

Date Issue

927578 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated
  • 2. Re: Date Issue
    927578 Newbie
    Currently Being Moderated

    but i didn't change any settings    

  • 3. Re: Date Issue
    Hoek Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

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

Legend

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