4 Replies Latest reply: Jan 26, 2013 8:00 AM by EdStevens RSS

    Date format problem

    804390
      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
          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
            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
              before upload anything use
              alter session set nls_date_format
              • 4. Re: Date format problem
                EdStevens
                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 …..