Re: Date format questions ...

Hi Jimmy,

Regardless of the format you specify Oracle uses the same internal format for actually storing the date. Unfortunately if you fail to specify the four digit year Oracle assumes that the date if for the current century. In order to avoid problems next year you should start modifying all of the applications which ask users to enter dates and/or write dates to tables so that they use four digit years. This is independent of the NLS_DATE_FORMAT that you specify, but you really should be consistent and convert everything. Once you get the applications and NLS_DATE_FORMAT you should consider updating your existing tables and converting the dates in them to four digit years.

(The facts in the above paragraph are extracted from Oracle 8 The Complete Reference by George Koch and Kevin Loney. The opinions and recommendations are mine.) I strongly suggest that you get the book.

Regards,

Jerry

Jimmy wrote:
>
> Hello all,
>
> I have a question about Oracle date format.
> Oracle Database now have a date format as 'DD-MON-YY'. If I change the
> NLS_DATE_FORMAT to 'DD-MON-YYYY', should the current application
> programs need to modify? This is becuase now the application programs is
> using 'DD-MON-YY' as the date format and these programs need to send SQL
> statements to the Oracle with the 'DD-MON-YY' date format, should I need
> to change these SQL statements with 'DD-MON-YYYY' date format?
 

-- 
Jerry Gitomer		Since I know how to spell DBA I became one. 
jgitomer_at_p3.net