Dates and Times

Format Suffixes

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


 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12359.html]SQL Snippets: Dates and Times - Format Suffixes [/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12359.html">SQL Snippets: Dates and Times - Format Suffixes </a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Dates and Times - Format Suffixes
  • URL (href): http://www.sqlsnippets.com/en/topic-12359.html