Date formatting

When a date value such as SYSDATE is displayed, Oracle implicitly converts the internal Julian format to a character string. The default display format as you have seen is DD-MON-YY. As noted earlier though, different date and time formats are required to suit different situations, so all database platforms provide a way to control the format in which the date/time value is presented.

In Oracle, the display of a date/time value makes explicit reference to the fact that there is a datatype conversion being performed. The general form of the formatting function is

    TO_CHAR(date_value, required_format)

where date_value is either a stored value, or a system value like SYSDATE, and required_format is a pattern made up format codes. The pattern in required_format takes the form of a character string including single quotes. We can explicitly format SYSDATE and the value that we stored in the test table to reveal the time components that were previously hidden:

        SELECT TO_CHAR(date_col,'DD-MON-YYYY HH:MI:SS'),
               TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
        FROM date_tab;

If you run this query several times, you will notice how the SYSDATE changes while the stored value remains the same.

A comprehensive list of formatting elements can be found in the SQL Reference Guide

A brief look at this list will tell you that there is a format for just about any date or time format you could need. The table below summarises some of the codes that you may find yourself using on a regular basis.

Format	Description	Example
DD	Day of the month	1 January = 1
FMDDTH	Ordinal day of the month	1 January = 1st
DAY	Name of the day	Monday
DY	Abbreviated day name	Mon
MM	Month number	January = 1
MONTH	Month name	January
MON	Abbreviated month name	Jan
YYYY	Four-digit year	2012
YY	Two-digit year	2012 = 12
HH	Hour (1-12)	1pm = 1
PM	Meridian indicator	1pm = PM
HH24	Hour (0-23)	1pm = 13
MI	Minutes past the hour
SS	Seconds past the minute

The Oracle SQL Reference provides some further examples of using date formats.