Skip to content

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'),
        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.