CODEWITHSUNDEEP
oracle-databasedateoracle11g
David Brower
David Brower

Reputation: 3048

Oracle Date to_char Returns Different Results

I have a database on my local development machine and there is a database on our test server. Basically, the tables on my dev machine were copied over from the test machine.

However, I have found a difference in how the same date is treated by the to_char function. On my development machine if I run the following query:

select test_date, to_char(test_date, 'YYYY-MM-DD')
from test.table
where id = 'C0007784'

I get the following results:

31-DEC-99 1999-12-31

On the test server running the same query against the same schema and data I get the following:

31-DEC-99 1899-12-31

Could this difference in behaviour of to_char be due to a setting being different in the two Oracle instances?

If I run SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT'; I get DD-MON-RR for both instances.

Upvotes: 0

Views: 506

Answers (1)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

So you exported the contents of the table to a csv file using DD-MON-YY format. YY obviously causes ambiguity. I guess that when you were importing the file, 99 was interpreted as 1999 instead of 1899. I don't know the exact mechanism which is used by database to guess the full year, but anyway Oracle strongly recommends YYYY in date format:

Note: Oracle recommends that you use the 4-digit year element (YYYY) instead of the shorter year elements for these reasons: The 4-digit year element eliminates ambiguity.

The shorter year elements may affect query optimization because the year is not known at query compile time and can only be determined at run time.

Upvotes: 2

Related Questions