In some DBMS (or only in the environment, not sure) one date format is used by default, in some - another. For example:

select * from subs_histories where start_date >'01-NOV-01'

Somewhere it will be executed correctly, but somewhere it will not recognize the date in the string.

How to understand which date format is used by default, without explicitly leading to a date type?

  • select * from subs_histories where start_date > '2001-11-01 will be executed always and everywhere - Anton Shchyrov
  • @AntonShchyrov "ORA-01861: literal does not match the format of the string" - Vitaly Yandulov
  • Quotation at the end is not forgotten? - Anton Shchyrov
  • @AntonShchyrov did not forget :) Without it, there would be "ORA-01756: there is no trailing quotation mark" - Vitaly Yandulov
  • 2
    date forgot before date :) See my answer. - Dmitriy

2 answers 2

With this form of recording:

 ... where start_date > '01-NOV-01'

there will be an implicit date conversion in accordance with the format specified in NLS_DATE_FORMAT for the current session, which you can find out like this:

 SQL> select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'; PARAMETER VALUE ----------------- ------------- NLS_DATE_FORMAT DD-MON-RR

That is, in fact, will be executed:

 ... where start_date > to_date ('01-NOV-01', 'DD-MON-RR')

It is almost impossible to achieve the installation of the same default value of the NLS_DATE_FORMAT parameter for all sessions where a request is made with a similar recording form.
Therefore, the entry form without explicitly specifying the date conversion format, or as mentioned in the question:

not explicitly leading to date type

not recommended and should be avoided .

You must either explicitly specify the date format, as in your case:

 to_date ('01-ЯНВ-01', 'DD-MON-RR')

Or set the date literal (date literal) in ANSI format 'YYYY-MM-DD' :

 ... where start_date > date'2001-11-01'

This format is independent of the NLS session or database settings and cannot be changed. This form of recording is preferred if you do not need to specify the time, because it is not in this format.

More in off. documentation .

  • Yes, I understand that you should not use it and will never use it. Just in the educational material from the oracle there was a code that I did not run. I was curious. - Vitaly Yandulov
  • Maybe in the educational material at this place it was just talked about implicit conversion. If not, then you need to choose another in the training material. - 0xdb 4:08 pm
  • Well, the official looks like an oracle one for preparing for the exam. - Vitaly Yandulov

Regional settings for the date, time, numbers and other parameters are met by the parameters that can be viewed in V$NLS_PARAMETERS :

 select * from V$NLS_PARAMETERS

Specifically for the date - the parameter NLS_DATE_FORMAT . You can install it as follows:

 alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

And check:

 select sysdate from dual; SYSDATE ------------------- 01.03.2018 16:02:10

In addition, ISO date format YYYY-MM-DD is always accepted and with any settings:

 select sysdate from dual where date '2018-01-01' < sysdate; SYSDATE ------------------- 01.03.2018 16:05:15

PS There is another tricky parameter NLS_TERRITORY , it depends on it which day of the week will be the first. Americans have this Sunday, Europeans have Monday.

  • Yes,> date '2001-11-01' works, but as for the parameter, but in the table NLS_DATE_FORMAT = DD-MON-RR, and> '01 -NOV-01 'still doesn't work ._. and> date '01 -JAN-01 'also does not work. - Vitaly Yandulov
  • figured out why he doesn't want to. Another parameter is NLS_DATE_LANGUAGE. He was Russian, so the date was requested by '01 -Janv-01 ' - Vitaly Yandulov
  • A bit confused. The date keyword always requires the format 'YYYY-MM-DD' ( dock ), and the NLS_DATE_FORMAT setting allows, among other things, to write to_date('<какой-нибудь формат>') without specifying the exact format. Well, there is still a lot of all sorts of subtleties associated with the fact that if you miss some of the characters in the text view of the date, oracle will still understand - Dmitriy