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
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:
1 2 3
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.
|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|
|MON||Abbreviated month name||Jan|
|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.