1

I would like to know which parameter in Oracle environment causes this :

Let's says I have two databases on two differents server. Same data in the databases.

Under sql developer I write this query :

select to_char(date_column, 'DD/MM/YYYY') from my_table;

When I check my_table in sql developer, the dates are displays with this format 'DD/MM/YY'

On one server the query returns : 01/05/1945 (it is good) But on the other one : 01/05/2045 (not good).

Question : what is the parameter in the configuration that is different ? what should be the value of this parameter in my ksh script to make it work on both bases ?

Note : the replacement of 20 instead of 19 only occurs for dates concerning the first 50 years of the century. Indeed, on both databases, 01/05/1955 is displayed.

Thanks

Résult of the dump function : try on this date 09/05/55

the good one :

09/05/1955 Typ=12 Len=7: 119,155,5,9,1,1,1

the bad one :

09/05/55 Typ=12 Len=7: 119,155,5,9,1,1,1

Is this called differently stored ? or the display is different. In the two cases the same bytes are stored but I am asking.

plus I would like to say that the way data is inserted in the database is by copy

Results of two queries asked :

on the good server:

NUM NAME               TYPE   VALUE
224 nls_date_language  2           
225 nls_date_format    2      DD/MM/YYYY

PARAMETER              VALUE
NLS_DATE_FORMAT        DD/MM/YYYY
NLS_DATE_LANGUAGE      AMERICAN

on the wrong one

NUM NAME               TYPE   VALUE
224 nls_date_language  2           
225 nls_date_format    2      

PARAMETER              VALUE
NLS_DATE_FORMAT        DD/MM/RR
NLS_DATE_LANGUAGE      FRENCH
mlwacosmos
  • 4,391
  • 16
  • 66
  • 114
  • it does not change anything – mlwacosmos Sep 22 '15 at 10:17
  • Sorry, I was thinking the wrong way around. You're converting to a character, which means that it's already a date, which means that the reason you're getting the data you are is because that's what the data in your database is. You must have converted using the `RRRR` or `RR` date format when putting the data into the database? – Ben Sep 22 '15 at 10:24
  • No it is not... the data are the same on the two databases. – mlwacosmos Sep 22 '15 at 10:28
  • Just to double check: Are you 100% sure you have a column of DATE type and the same data in both servers? If the column is CHAR or VARCHAR, that may explain this. – GTG Sep 22 '15 at 10:35
  • It sounds like the data was inserted with a YY format in one database and a YYYY format in the other. – Gordon Linoff Sep 22 '15 at 11:00
  • I am 100% sure.. well 99% because I did not copy the databases myself but the production center – mlwacosmos Sep 22 '15 at 11:07
  • What mechanism did they use to copy the database? – David Aldridge Sep 22 '15 at 11:56

2 Answers2

1

The reason of different output is different data in your databases (hope type of date_column is date).
Use this query to check this:

select date_column, dump(date_column) from my_table;

dump function will return datatype, column length and bytes that actually stored.
The reason why these 2 dates are different probably in way of inserting.
If you use string conversion to date and string contain only 2 digits for year, then different result may occur. Even 2 different formats exists for such string YY and RR. http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
here is list of date format elements with details of how to use them.
In date format Oracle stores complete year so ambiguous translation to_char is impossible
(here is description of How are dates stored in Oracle?)

Community
  • 1
  • 1
Anton Zaviriukhin
  • 741
  • 4
  • 14
  • yes, I agree, the most likely reason is the use of a 2 digit year at input where the wrong century has been assumed and stored (hence the 100 year difference) – Paul Maxwell Sep 22 '15 at 11:28
  • That's really strange. Date stores correctly - 119-100 = 19 (century), 155-100=55 (year) 1955 as a result. – Anton Zaviriukhin Sep 22 '15 at 13:25
  • Try ALTER SESSION SET nls_date_format = 'DD/MM/YYYY'; on server with issue, and then run again your query - it should return correct result – Anton Zaviriukhin Sep 22 '15 at 13:32
  • Could you send results of following queries (from new session without alter session) SELECT * FROM v$parameter WHERE NAME LIKE 'nls_date%'; SELECT * FROM v$nls_parameters WHERE parameter LIKE 'NLS_DATE%'; – Anton Zaviriukhin Sep 22 '15 at 14:28
  • I tried SET nls_date_format = 'DD/MM/YYYY'; but it does not change anything – mlwacosmos Sep 22 '15 at 14:59
  • Finally It is possible that I exported and imported the table with sql developer. Because of the queries Anton gave us, it probably used the default nls_date_format value (DD/MM/RR) and this is where I lost the information when I imported data back to the table... Thanks to Anton for the good function dump – mlwacosmos Sep 22 '15 at 15:21
  • @mlwacosmos You didn't lose any data -- the dump proves that the data is the same in both systems. You're just seeing a different implicit conversion to string format because you're inheriting different NLS configurations. – David Aldridge Sep 22 '15 at 15:23
  • @david : you sound right but why do I still have the same wrong conversion under sql plus whereas I write : ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'; before the query – mlwacosmos Sep 22 '15 at 15:38
  • Select both the date, and the dump of the date, and the to_char(..., 'YYYY-MM-DD HH25:MI:SS') of the dates. – David Aldridge Sep 22 '15 at 15:42
1

Based on the value of the dumps, the values are both stored correctly. The difference must lie in the NLS settings of:

  1. The server
  2. The client
  3. The tool.

If you are just looking at the table contents in SQL Developer, and that is where the display difference occurs, then the best approach to take is to specifically set the display option for SQL Developer itself.

How can I set a custom date time format in Oracle SQL Developer?

The only way to be sure that you get the format that you want, and this goes for server side applications as well as client side, is for the application itself to set its required NLS parameters. Otherwise implicit conversions will always have the potential to be changed by an external config item.

If you are working in a software development environment, then I would recommend that you set your NLS_DATE_FORMAT to the ISO format of "YYYY-MM-DD HH24:MI:SS", so it is completely unambiguous, and any time elements on the date are always displayed.

Community
  • 1
  • 1
David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • As I understand, mlwacosmos use to_char function, so database convert date to char and SQL Developer return char. How database may incorrectly convert date to char? – Anton Zaviriukhin Sep 22 '15 at 14:15
  • @AntonZaviriukhin: "When I check my_table in sql developer, the dates are displays with this format 'DD/MM/YY'". Maybe the question is unclear, and the difference is when the data is viewed through a table browser. It's a more likely explanation than to_char rendering different strings with the same data and format picture. – David Aldridge Sep 22 '15 at 14:27
  • the problem is not in sqldeveloper. In sql plus it is the same : the dates are displayed differently betwenn the two servers – mlwacosmos Sep 22 '15 at 14:59
  • You mean "the problem is not only in SQL Developer". In fact you have problems with both applications, because neither of them are specifying the NLS parameters that you want. You can set them for the client machine, but then another application on that machine may set them to something different itself. The only way to be sure, and this goes for server side applications as well as client side, is for the application itself to set its required NLS parameters. Otherwise implicit conversions will always have the potential to be changed by an external config item. – David Aldridge Sep 22 '15 at 15:11
  • What I mean is that sql developer is not the problem bacause it seems to use the nls_date_format that is set in the database. I did not realised before but indeed on the good server with nls_date_format = DD/MM/YYYY, sql developer displays good and on the other one it uses the parameter stored DD/MM/RR : that is why I just have 2 digits when I look at the tables with the tool – mlwacosmos Sep 22 '15 at 15:26
  • Yes, that's correct, but unless you specifically set the NLS_DATE_FORMAT in SQL Developer, you have no guarantee that it will represent the date in the way that you want. For example it is pretty rare for NLS_DATE_FORMAT on a server or client to specify a time component as well, but as a developer it can be critical to see whether there is a time component, and what it is. SO is full of problems caused by developers not realising that there is a time component on an Oracle date. – David Aldridge Sep 22 '15 at 15:30