0

Oracle 11g documentation says that default date format is DD-MON-YYYY, which means that if I insert date to a date column using: 

insert into table t values(1, '02-JAN-2013')

and then select it 

select * from t

it should display as 02-JAN-2013 whereas in reality it displays date as 01/02/2013.

Why his discrepancy or am I missing something.

Ref. http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

FYI. I am using PL/SQL Developer for this experiment.

user204069
  • 1,215
  • 3
  • 19
  • 25

2 Answers2

2

There isn't really a default date format for the product, the reference guide states that the default for NLS_DATE_FORMAT at system level is based on NLS_TERRITORY. Though this says the 'standard' is `DD-MON-RR', and I think that's what you get if you don't specify a value at database level, at least on the platforms I've worked on.

When you create a new database the initialisation parameters can include a specific NLS_DATE_FORMAT, and that can be changed later too.

But the system-level NLS_DATE_FORMAT can be overridden by the client, the session, or in a TO_CHAR call. You can look in v$nls_parameters to see the system value, and nls_session_parameters to see your current session value. You can change that with alter session if you want to.

I'm pretty sure you'll find that PL/SQL Developer is setting the session NLS_DATE_FORMAT to MM/DD/YYYY somewhere in its preferences.

Generally it's better to not rely on that value at all, and always use an explicit format mask for display, e.g. TO_CHAR(<column>, 'YYYY-MM-DD HH24:MI:SS').

There's more on NLS_DATE_FORMAT in the globalisation guide here and here; and a bit about date display here; and an overview of the date format model elements here

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • If this is the case then why I have to use specific format while comparision of dates: Eg. select si.effective_date from search_instance si where si.effective_date > '26-JUN-2013' order by si.effective_date . If instead of '26-JUN-2013' I use '06/26/2013' it will say 'Not a valid Month'. – user204069 Jun 26 '13 at 17:51
  • 1
    To answer your comment please read my answer [here](http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql/10178346#10178346) or [here](http://stackoverflow.com/questions/15970277/comparing-with-date-in-oracle-sql/15970516#15970516) (I'm sure Alex has as many) @user204069... You're ***not*** comparing dates there, you're comparing strings. _Always_ explicitly convert strings to a date for date comparisons. – Ben Jun 26 '13 at 18:07
  • 1
    @user204069 - what is your session value? You're doing an implicit conversion from a string to a date, which you should avoid as it's ambiguous. You should use `to_date('06/26/2013', 'MM/DD/YYYY')`. Or possibly `date '2013-06-26'`, using an ANSI date literal. – Alex Poole Jun 26 '13 at 18:07
1

It depends of NSL_DATE_FORMAT that depends of NLS_TERRITORY

Give a look to: http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm

Aguardientico
  • 7,641
  • 1
  • 33
  • 33
  • If this is the case then why I have to use specific format while comparision of dates: Eg. select si.effective_date from search_instance si where si.effective_date > '26-JUN-2013' order by si.effective_date . If instead of '26-JUN-2013' I use '06/26/2013' it will say 'Not a valid Month'. – user204069 Jun 26 '13 at 17:58
  • I try to use always the ansi date format: 'YYMMDDHHMISS' with this, i do not have to check what format the DB use. Then i can use something like `si.effective_date > '20130626000000'` – Aguardientico Jun 26 '13 at 18:07