oracle date format

[MURALI KRISHNA]

i check ( nls_date_format ) from select * from nls_session_parameters

in that table date format is DD-MON-yyyy

if i execute select query select hiredate from emp;
it displays date in the format mon-dd-yyyy

why it displays in this format and where it configured?

 

[Marco Kalter]

The PL/SQL Developer date format can be defined through Tools > Preferences > User Interface / NLS Options.

 

[MURALI KRISHNA]

Thank you Marco Kalter for your time.

 

[liran]

Hi

I have tried what you suggested and have changed the format and it solves the problem for selects however when I run a pl/sql script it returns the date format in the default date.
How can I get this to change as well?

Thanks
Liran

 

[Marco Kalter]

Your Oracle session will use the NLS_DATE_FORMAT as defined in the Oracle Registry (or as environment variable).

 

[liran]

thanks for the quick reply.
when i am in a session on the server it self I can change the settings with alter session set and this works but when I am in the pl/sql developer and I run the procedure it returns the incorrect date eventhough I have set the correct format in the preferences tab.

Liran

 

[Marco Kalter]

The preferences only affect the client side date format. The server side date format needs to be controlled by the NLS_DATE_FORMAT in the registry or environment variable.

 

[Maxmix]

In Tools\Preferences\User Interface\NLS Options I want to choose Oracle format. But result grids of SQL Window show dates incorrectly I think, and don't obey alter session set nls_date_format commands, issued in SQL Window.
The format I get regardless of anything is
dd-mes-yyyy hh24:mi:ss, where mes - is a Russian abbreviation of month's name.

By default (without attempt to change nls_date_format within the session), NLS settings are:

SQL>
select a.parameter, a.value from nls_session_parameters a;  --where 'nls_date_format'=lower(parameter)
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
17 rows selected

I have no settings on client side for NLS_DATE_FORMAT, and have the option "dates to_char" turned OFF.

TERRITORY and LANGUAGE are AMERICA/AMERICAN both on the server and on the client.

Where am I wrong? How could I change output to dd.mm.yyyy ?

 

[Marco Kalter]

The client-side date format is determined once during startup. Subsequent "alter session" commands do not affect it. For the SQL Window you can set the "Date fields to_char" preference, so that all dates are formatted on the server using the current NLS_DATE_FORMAT.

 

[Maxmix]

I see, but it's inconvenient. I probably will set parameter in the Registry, but could you please guess, where my formatting comes from? I mean, why this specific format with Russian months names, it really confuses me.

 

[Marco Kalter]

I assume that is your Windows date format?

 

[Maxmix]

No, at least I see another formatting when I choose Windows option in
Tools\Preferences\User Interface\NLS Options (and "long" format in Windows Control Panel is another different case).

It's OK for me to use Windows style, but it's become interesting: what the option Oracle means in NLS Options panel?

 

[Marco Kalter]

"Oracle" means that the NLS_DATE_FORMAT for the Oracle Client is used for client-side date formatting. It does not dynamically pick up alter session statements that change the NLS_DATE_FORMAT though.

 

[Maxmix]

"Oracle" means that the NLS_DATE_FORMAT for the Oracle Client is used for client-side date formatting

It's clear now, thanks.
But we couldn't solve a mystery of my weird format...