René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

to_char and to_nchar [Oracle SQL]


to_char(datetime-or-interval, 'format-string')
to_char(datetime-or-interval, 'format-string', 'nlsparam')

to_char(number,'format-string', 'nlsparam')
TO_CHAR is an SQL function that converts a string, date, a datetime-interval or a number into a character string.

Converting a date

If the first parameter is date type, it can be converted to a string representation. The appearance of that representation can be controlled with a Datetype format elements which are (optionally) in the second parameter.
select to_char(sysdate, ' hh24:mi:ss') from dual;

Format strings

Possible format strings when the first argument to to_char is a date:

Converting numbers

Suppressing leading or trailing blanks

If a number is formatted with 0000, a blank will be added on the left side if the number is positive. This is to make room for the minus symbol for numbers that are negative.
In order to suppress this blank, the format FM can be used.

Converting a number into a hex string

select to_char('419','xxx') from dual;

Converting dates

Abbriviated name of the day

The abbrivation of a day's name (such as SUN, MON ... can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.
select to_char(sysdate,'DY','nls_date_language=english') from dual

Spelling a number

SQL> select to_char(to_date('429','J'),'Jsp') from dual;

Four Hundred Twenty-Nine

Weekday (day of week)

to_char(some_date,'D') returns a number indicating the weekday. However, for a given date, this number returned varies with NLS_TERRITORY:
SQL> alter session set nls_territory='UNITED KINGDOM';

Session altered.
SQL> select to_char(sysdate,'D') from dual
SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> select to_char(sysdate,'D') from dual;
I executed these select statements on a thursday. So, in America, Thursday seems to be the 5th day in the week while it is the 4th in the UK.
The behaviour of to_char and to_nchar can be influenced by setting the NLS_DATE_LANGUAGE, NLS_CALENDAR, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_DUAL_CURRENCY and NLS_ISO_CURRENCY NLS parameters.

nls parameters

select to_char(444945.200, '99999999d00', 'nls_numeric_characters='', ''') from dual;


Thanks to Olivier Holl who notified me of a type on this page.