DATE column not displayed correctly

[S. Studeny]

Hello.

Developer is not displaying correct DATE format.

 

[Hilarion]

WoW!
Could you post your PL/SQL Developer formatting settings. Mostly "NLS Options" and if "Date fields to_char" is set.
You could also post your database session NLS settings (from SYS.NLS_SESSION_PARAMETERS), database version, Oracle client version, PL/SQL Developer version, etc.

 

[S. Studeny]

Character Sets
Character size: 4 byte(s)
CharSetID: 873
NCharSetID: 2000
Unicode Support: True
NLS_LANG: CZECH_CZECH REPUBLIC.EE8MSWIN1250

NLS_DATE_LANGUAGE CZECH
NLS_DATE_FORMAT DD.MM.RR
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR

SQL*Plus or Oracle SQL developer is displaying that type of column OK.

Also internal representation of DATE column is correct according to output from DUMP function.

Regards,
Stanislav

 

[Hilarion]

Which PL/SQL Developer version? Which Windows version? And PL/SQL Developer NLS options? I mean "Tools" / "Preferences" / "User Interface" / "NLS Options". What about "Tools" / "Preferences" / "Window Types" / "SQL Window" / "Date fields to_char"?

If in "Tools" / "Preferences" / "User Interface" / "NLS Options" / "Date" or "Time" you have "Windows format" selected, then try changing that to "User defined" and type in your preferred format (in Windows notation), like "yyyy-MM-dd" for date and "hh:mm:ss" for time. Maybe there's something wrong with your Windows settings or in the way the PL/SQL Developer reads those settings.

I also use Windows-1250 code page (or UTF-8) and I do not have any problems with date/time/timestamp data - PL/SQL Developer displays those correctly. I do have non-default date formatting settings thou (in PL/SQL Developer and in session NLS settings too), so I may not be a representative example. I'm using version 10.0.5.1710 version of PL/SQL Developer.

 

[S. Studeny]

PL/SQL Developer Version 10.0.5.1710 - NLS Options - Date & Time - set to windows format. No to_char conversion allowed for DATE columns.

Workaround is to retype column like this :

to_date(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(i."UbytovaniOd")),'dd.mm.yy')

then i get correct DATE output in PL/SQL Developer.

Regards,
Stanislav

 

[IvanZ]

Interesting.

What is your output for
select to_date('19000101' ,'yyyymmdd') from dual;
select to_date('29991231' ,'yyyymmdd') from dual;
?

 

[S. Studeny]

Output is correct.

1.1.1900
31.12.2999

Select I used to reproduce the error is based on VIEW using SELECT over DB link to DG4ODBC gateway. The point is, that DATE column is returned in correct format according to DUMP function.

Typ=12 Len=7: 120,113,12,8,1,1,1

According to Oracle's Doc ID. 69028.1

DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's DATE storage structure:

BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year
3 Month -- stored in 0 base notation
4 Day
5 Hour -- stored in excess-1 notation
6 Minute
7 Second

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute
number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date again:
17-DEC-1980 00:00:00

we would expect this date to be stored internally as follows:
119, 180, 12, 17, 01, 01, 01

There is also external DATE representation (Typ=13) "equivalent" to C-style structure .. but this is out of this topic.

Regards,
Stanislav