Specifying NLS parameter to be used
The third parameter of
to_char
(when converting dates or numbers to strings) allows to specify
NLS parameter values for the conversion.
select
to_char(sysdate, 'Dy' , ' nls_date_language=english' ) day_english,
to_char(sysdate, 'Dy' , ' nls_date_language=german' ) day_german ,
to_char(42.42 , '99d99', q'[nls_numeric_characters=', ']') comma ,
to_char(42.42 , '99d99', q'[nls_numeric_characters='.,']') dot
from
dual;
--
-- DAY_ENGLISH DAY_GERM COMMA DOT
-- ------------ -------- ------ ------
-- Sat Sa 42,42 42.42
Inserting a literal character or string
The following statement produces an ORA-01821: date format not recognized error:
select
to_char(sysdate, 'hh24 hr, mi min, ss sec')
from
dual;
This is because some characters or strings that the statement tries to be literally inserted into the result must be quoted with double-quotes
The following example does not produce an error:
select
to_char(sysdate, 'hh24 "hr," mi "min," ss "sec"')
from
dual;
--
-- 15 hr, 01 min, 57 sec