CODEWITHSUNDEEP
oracle-databasedate
luk_asz94
luk_asz94

Reputation: 13

oracle date format (auto convert)

I have problem with understanding one issue. Assume that a parameter nls_date_format is set to dd-mon-rr, then if there is a table with column which contains date type I can insert data there only if statement looks like this:

insert into table values ('01-jan-00')

or '01-january-00' instead of jan. Date 01-01-00 cannot be inserted (invalid month).

On the other site, when the nls_date_format parameter is set to dd-mm-rr I can insert date in format either '01-jan-00' or '01-01-01' or even '01-january-00' . There is no invalid month error. So how does it work, oracle can convert date from string (january) to number(01), but cannot convert date from number (01) to string(january)?

Upvotes: 1

Views: 2669

Answers (1)

krokodilko
krokodilko

Reputation: 36087

This is explained in the documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

String-to-Date Conversion Rules
.....
.....

  • If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-18.
**Table 2-18 Oracle Format Matching**
Original Format Element | Additional Format Elements to Try in Place of the Original
------------------------|-----------------------------------------------------------
'MM'                    | 'MON' and 'MONTH'
'MON'                   | 'MONTH'
'MONTH'                 | 'MON'
'YY'                    | 'YYYY'
'RR'                    | 'RRRR'

According to the above, all conversions listed below work without any error:

alter session set NLS_LANGUAGE='American';

select to_date('21-jan-2014',     'dd-month-yyyy' ) from dual;
select to_date('21-january-2014', 'dd-mon-yyyy' )   from dual;

select to_date('21-january-2014', 'dd-mm-rrrr' ) from dual;
select to_date('21-jan-2014',     'dd-mm-rrrr' ) from dual;

select to_date('21-01-2014', 'dd-mm-yy' )   from dual;
select to_date('21-01-14',   'dd-mm-yyyy' ) from dual;

select to_date('21-01-2014', 'dd-mm-rr' )   from dual;
select to_date('21-01-14',   'dd-mm-rrrr' ) from dual;


alter session set NLS_LANGUAGE='Polish';

select to_date('21-sty-2014',     'dd-month-yyyy' ) from dual;
select to_date('21-styczeń-2014', 'dd-mon-yyyy' ) from dual;

select to_date('21-styczeń-2014', 'dd-mm-rrrr' ) from dual;
select to_date('21-sty-2014',     'dd-mm-rrrr' ) from dual;

Upvotes: 2

Related Questions