This discussion is archived
4 Replies Latest reply: Jan 26, 2013 6:00 AM by EdStevens RSS

Date format problem

804390 Newbie
Currently Being Moderated
Hi I have database jobs that upload the data in my applications.

My problem is while copying the record of one application to another, the format of dates goes wrong

For example:

The date in one column is 01-JAN-1941
but in the other record while copying ot goes as 01-JAN-2041.

Pls suggest.
  • 1. Re: Date format problem
    ranit B Expert
    Currently Being Moderated
    user13355933 wrote:
    the format of dates goes wrong
    The date-format looks absolutely fine, in both cases being +'dd-MON-yyyy'+
    My eyes don't see anything wrong in the format.

    As far as '1941' and '2041' is concerned, please let us know what exactly is your application doing and check the copy process carefully.
  • 2. Re: Date format problem
    j_DBA_sourav Newbie
    Currently Being Moderated
    Hello,

    I have seen one application user getting this kind of error too.. Please ask to check the application code.. if possible I think good way to treat the date format is YYYY-MM-DD.. The time I saw the problem in my DB we found the work around like this way only. Just check your luck on this too :)

    -Regards,
    J_DBA

    Edited by: j_DBA_sourav on Jan 26, 2013 4:15 AM
  • 3. Re: Date format problem
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    before upload anything use
    alter session set nls_date_format
  • 4. Re: Date format problem
    EdStevens Guru
    Currently Being Moderated
    user13355933 wrote:
    Hi I have database jobs that upload the data in my applications.

    My problem is while copying the record of one application to another, the format of dates goes wrong

    For example:

    The date in one column is 01-JAN-1941
    but in the other record while copying ot goes as 01-JAN-2041.

    Pls suggest.
    And what are the data types of the columns? And if the data passing through any systems outside of the database, what is the format there?

    For a better understanding of DATE formats and the difference between storage of dates and presentation (text representation) of dates, please read [url http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/] But I want to store the date in format …..

Legend

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