0

query is :

select substr(to_date('01-02-2018','mm-dd-yyyy'),4,3) from dual;

output is : 

JAN

can anyone explain how the output came ?

forpas
  • 160,666
  • 10
  • 38
  • 76
Sambhav Jain
  • 23
  • 1
  • 3
  • Why don't you just use `substr('01-02-2018',4,3)` and get rid of the date conversion? –  Feb 21 '20 at 10:24

1 Answers1

4

When you apply a text function directly to something that's of DATE datatype, you force an implicit conversion of the date into a string. This conversion uses the NLS_DATE_FORMAT parameter to decide the format of the output string.

In effect,

substr(to_date('01-02-2018','mm-dd-yyyy'),4,3)

is the same as

substr(to_char(to_date('01-02-2018','mm-dd-yyyy'), <NLS_DATE_FORMAT>),4,3)

The usual default value (for English-language versions of the database) of the NLS_DATE_FORMAT parameter is DD-MON-RR - which it sounds like the value of your NLS_DATE_FORMAT parameter is set to, which means your query is doing:

substr(to_char(to_date('01-02-2018','mm-dd-yyyy'), 'DD-MON-RR'),4,3)

Therefore, the substr is working on the string 02-JAN-18, and the 3 characters starting from the 4th character is JAN.

Rather than use substr on a date, you would do better to use to_char instead, e.g.:

to_char(to_date('01-02-2018', 'mm-dd-yyyy'), 'MON')
Boneist
  • 22,910
  • 1
  • 25
  • 40
  • 2
    The [default `NLS_DATE_FORMAT`](https://stackoverflow.com/a/50164234/1509264) depends on the `NLS_TERRITORY` so, for many countries of the world, your generalisation "The usual default value of the `NLS_DATE_FORMAT` parameter is `DD-MON-RR`" does not hold true. – MT0 Feb 21 '20 at 09:44
  • I've clarified. – Boneist Feb 21 '20 at 09:46