Summary: in this tutorial, you will learn about the Oracle date format and various format elements for formatting date data.
Introduction to Oracle Date Format Model
Oracle Database uses an internal format for storing
DATE data. Therefore, before inserting date data in a non-standard format into the database, you have to use the
TO_DATE() function to convert it to the Oracle’s internal date format.
Similarly, after querying date data from the database, you have to format it to make the date data human-readable. To format a date value, you use the
TO_CHAR() functions use something called date format model that describes the format of the date data stored in a character string.
Suppose, you have a literal string that represents a date as follows:
Code language: HTML, XML (xml)
The date format model for this string is:
Code language: SQL (Structured Query Language) (sql)
A date format model is composed of one or more date format elements. In this example, the format elements are:
DD represents the day,
Mon represents the abbreviated name of the month,
YYYY represents the 4-digit year, and the character string literal
'-' represents the hyphen character.
Date Format Elements
If you use a date format element in a specific case e.g., uppercase, lowercase, etc., the output will be in the corresponding case.
For example, the date format element
'DAY' returns uppercase word like
The following table illustrates the common date format elements. The example is based on the the date value
|AD indicator with or without periods.||AD or A.D.|
|Meridian indicator with or without periods.||AM or A.M.|
|BC indicator with or without periods.||BC or B.C.|
|Two-digit century. If the last 2 digits of a y-digit year are 00, the century is the same as the first 2 digits e.g., 2000 returns 20. In case the last 2 digits are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year e.g., 2001 returns 21.||2001 returns 21; 2000 returns 20.|
|D||Day of week (1-7). The first day of week can be Sunday or Monday depending on the value of the ||1|
|DAY||Name of a day which is controlled by the value of the ||Monday|
|DD||Day of month (1-31)||15|
|DDD||Day of year (1-366)||100|
|DL||Long date format determined by the ||Tuesday, August 01, 2017|
|DS||Short date format controlled by the ||8/1/2017|
|DY||Abbreviated name of the day.||TUE|
|E||Abbreviated era name e.g., Japanese Imperial, ROC Official, etc.|
|EE||Full era name e.g., Japanese Imperial, ROC Official, etc..|
|FF [1..9]||Fractional seconds|
|FM||Returns a value with no leading or trailing blanks.|
|FX||Requires exact matching between the character data and the format model.|
|Hour of day (1-12).||03|
|HH24||Hour of day (0-23).||15|
|IW||Week of the year (1-52 or 1-53) based on the ISO standard.||31|
|Last 1, 2, or 3 digits of ISO year.||7 for I, 17 for IY, and 017 for IYY|
|IYYY||4-digit year based on the ISO standard.||2017|
|J||Julian day, which is the number of days since January 1, 4712 BC.||2457967|
|MI||Minute that ranges from 0 to 59||30|
|MM||Month that ranges from 01 through 12, where January is 01.||08|
|MON||Abbreviated name of the month.||AUG|
|MONTH||Name of the month.||AUGUST|
|Meridian indicator with or without periods.|
|Q||Quarter of year (1, 2, 3, 4; January – March = 1).||3|
|RM||Roman numeral month (I-XII; January = I).||VIII|
|RR||allows you to store 20th-century dates in the 21st century using only two digits.|
|RRRR||Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.|
|SSSSS||Seconds past midnight (0-86399).||55820|
|TS||Tim in the short time format, depending on the ||3:30:00 PM|
|Daylight savings information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in || |
|Time zone hour. (See ||‘HH:MI:SS.FFTZH:TZM’|
|Time zone minute. (See |
|Time zone region information. The value must be one of the time zone regions supported in the database. Valid in timestamp and interval formats, but not in |
|The week number of a year that ranges from 1 to 53. The week 1 starts on the first day of the year and continues to the seventh day of the year.||31|
|The week number of a month that ranges from 1 to 5. The week 1 starts on the first day of the month and ends on the seventh.||1|
|4-digit year with a comma.||2,017|
|Year, spelled out; ||TWENTY SEVENTEEN|
|4-digit year; ||YYYY returns 2017|
|The last 1, 2, or 3 digits of a year.||Y returns 7, YY returns 17, and YYY returns 017|
Besides the standard date format elements, you can include the following character in the date format model.
- Punctuation such as hyphen (-), slash (/), comma (,), period (.) and colons (:)
- Character string literals enclosed in double quotation marks.
These punctuation and characters appear in the return value in the same location as they appear in the format model.
It is not valid to use the same format element twice and combine format elements that represent the similar information for input date models. For example, you cannot use both ‘SYYYY’ and ‘BC’ in the same date format model.
In this tutorial, you have learned how to use the Oracle Date Format to format date data for storing and displaying.