Oracle SQL - Formatting dates into a more usable form (TO_CHAR)

Dates in oracle are stored in a format that doesn’t always make them friendly from a reporting point of view or dealing with data in Excel point of view, the TO_CHAR function can reformat your datetime fields so that they are more user friendly.

Examples

The statement is written with two parameters the exact name of the date field and the format (see second table).

TO_CHAR(DATEFIELD,’FORMAT’)

Statement	Result
TO_CHAR(DATEFIELD,’dd mm year’)	23 05 twenty thirteen
TO_CHAR(DATEFIELD,’dd/mm/yy’)	23/05/2013
TO_CHAR(DATEFIELD,’Day Month Year’)	23 May Twenty Thirteen
TO_CHAR(DATEFIELD,’dd/mm/yyyy hh24:mi’)	23/05/2013 18:38

Example formats

Any combination of the following formats can be used, although obviously certain combinations may not make any sense to your end users.

Format	Description	Example
Year/year	Year spelt out in text (with and without an upper case first digit	Twenty Thirteen / twenty thirteen
yyyy	Year number in full	2008
yy	Last two digits of year number	08
q	Quarter of the year	1 (February)
mm	Month number within year**	12 (December)
Mon / mon	Abbreviated month name (with and without an uppercase first digit)	Oct/oct
Month / month	Month name (with and without an upper case first digit)	October / october
w	Week number* (within month)	1 (01/05/2013)
ww	Week number* (within year) **	18 (01/05/2013)
d	Day of the week	1 (Monday)
dd	Day number within month**
Dy/dy	Abbreviated day name (with and without an uppercase first digit)	Mon / mon
Day / day	Day name (with and without an upper case first digit)	Monday / Monday
hh24	Hour of the day in 24 hour format**	16 (4pm)
hh	Hour of the day in 12 hour format**	04 (4pm)
mi	Minute of the hour**	52 (16:52)

*Important note about week numbers

Week numbers in oracle can be confusing as they start on the first day of the year and count seven days and then change, for example in 2013 the week number would increase each Wednesday.

**Suppression of zeros

This format can be prefixed with FM to suppress zeros, for example mm would display January as “01” where as if FMmm was used it would show “1”.

How to test you date formatting

The easiest method for testing the reformatting of your dates is to test them using the dual function, run the following SQL statement and substitute the area highlighted in yellow with the date format you are trying to use.

select to_char(to_date('01/05/2013 16:30','dd/mm/yyyy hh24:mi'),'Year') from dual