Search notes:

Oracle SQL: TO_CHAR

to_char converts a number, date/datetime, timestamp/etc or character value to a varchar2.
to_char(number|date|datetime [, format [, 'nls-parameter']])
to_char(nchar|clob|nclob|)

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

See also

Formatting a timestamp.
The error message ORA_00932: inconsistent datatypes: expected NLS PARAMETER got NUMBER
The default date format string for to_char is specified with the nls_date_format setting.
The error messages
Oracle SQL functions
Oracle format models for conversion functions
to_nchar

Index