Saturday, 14 February 2009

Oracle Quick Tip: Datetime Format Element Suffixes

The TH datetime format element suffix allows the ordinal number for part of a date to be displayed.
SELECT to_char(sysdate, 'ddth') d FROM dual;
D    
------
15th
1 rows selected

The SP suffix spells out the number in full.
SELECT to_char(sysdate, 'ddsp') d FROM dual;
D            
---------
fifteen
1 rows selected

These can be combined to produce the spelled, ordinal number.
SELECT to_char(sysdate, 'ddthsp') d FROM dual;
D              
-----------
fifteenth
1 rows selected

Or something a little more complex.
SELECT 
to_char(sysdate, '"The" ddspth "day of the" mmspth "month", yyyysp') d
FROM dual;
D
----------------------------------------------------------
The fifteenth day of the tenth month, two thousand eight
1 rows selected

1 comment:

Anonymous said...

how to spell number like 17