SQL Snippets ™: Tutorials for Oracle Developers

Datetime Format Element Suffixes can be used to format datetime value output. They cannot be used with input format models (e.g. with TO_DATE).

The available suffixes, and their meanings, are:

TH   - converts numbers to ordinal numbers

SP   - converts numbers to words

SPTH - converts numbers to ordinal words
 

Note that return values are always in English when these suffixes are used.

Here are some examples.

column output format a80

select
  to_char
  ( timestamp '2007-04-03 02:01:01', 'FMyyyyTH, mmTH, ddTH, hhTH, miTH, ssTH' ) as output
from dual ;
 

OUTPUT
--------------------------------------------------------------------------------
2007th, 4th, 3rd, 2nd, 1st, 1st
 

select
  to_char
  ( timestamp '2000-04-03 02:01:00', 'mmSP, ddSP, hh24SP, miSP, ssSP' ) as output
from dual ;
 

OUTPUT
--------------------------------------------------------------------------------
four, three, two, one, zero
 

select
  to_char
  ( timestamp '2000-04-03 02:01:00', 'mmSPTH, ddTHSP hh24SPTH, miTHSP, ssSPTH' ) as output
from dual ;
 

OUTPUT
--------------------------------------------------------------------------------
fourth, third second, first, zeroeth
 

See Transformations: Numbers to Words for techniques that use the SP feature to convert numbers to words.

Gotchas

Special Values

The formatting suffixes appear to have problems dealing with some values like zeros and negative years (as tested in Oracle 10g XE).

select to_char( timestamp '2007-04-03 02:01:00', 'ssTH' ) as output
from dual ;
select to_char( timestamp '2007-04-03 02:01:00', 'ssTH' ) as output
                *
ERROR at line 1:
ORA-01877: string is too long for internal buffer


 

select to_char( timestamp '-1001-01-01 00:00:00', 'syyyySP' ) as output
from dual ;
select to_char( timestamp '-1001-01-01 00:00:00', 'syyyySP' ) as output
                *
ERROR at line 1:
ORA-01877: string is too long for internal buffer