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() function.
Both TO_DATE() and TO_CHAR() functions use something called a 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:
'31-Dec-1999'Code language: JavaScript (javascript)The date format model for this string is:
'DD-Mon-YYYY'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 words like TUESDAY; 'Day' returns 'Tuesday'; and 'day' returns 'tuesday'.
The following table illustrates the common date format elements. The example is based on the date value 01-AUG-2017 15:30:20
| Element | Description | Example (*) |
|---|---|---|
| AD A.D. | AD indicator with or without periods. | AD or A.D. |
| AM A.M. | Meridian indicator with or without periods. | AM or A.M. |
| BC B.C. | BC indicator with or without periods. | BC or B.C. |
| CC SCC | 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 | Month that ranges from 01 through 12, whereas January is 01. | 1 |
| DAY | Name of a day which is controlled by the value of the NLS_DATE_LANGUAGE parameter | Monday |
| DD | Day of month (1-31) | 15 |
| DDD | Day of year (1-366) | 100 |
| DL | Long date format determined by the NLS_DATE_FORMAT parameter. Only with the TS element, separated by white space. | Tuesday, August 01, 2017 |
| DS | Short date format controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters. Only use with the TS element, separated by white space. | 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. | |
| HH HH12 | 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 |
| IYY IY I | 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. January starts from 01. | 08 |
| MON | Abbreviated name of the month. | AUG |
| MONTH | Name of the month. | AUGUST |
| PM P.M. | Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provide the same return as RR. If you do not want this functionality, then enter the 4-digit year. | |
| 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 | 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 DATE formats. | |
| SS | Second (0-59). | 20 |
| SSSSS | Seconds past midnight (0-86399). | 55820 |
| TS | Tim in the short time format, depending on the NLS_TERRITORY and NLS_LANGUAGE parameters. Only use with the DL or DS element, separated by white space. | 3:30:00 PM |
TZD | PST (for US/Pacific Standard Time); PDT (for US/Pacific daylight time) | The week number of a year 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. |
TZH | Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats. | ‘HH:MI:SS.FFTZH:TZM’ |
TZM | Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats. | |
TZR | 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 DATE formats.Example: US/Pacific | |
WW | The week number of a month ranges from 1 to 5. The week 1 starts on the first day of the month and ends on the seventh. | 31 |
W | 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 |
Y,YYY | 4-digit year with a comma. | 2,017 |
YEAR SYEAR | Year, spelled out; S prefixes BC dates with a minus sign (-). | TWENTY SEVENTEEN |
YYYY SYYYY | 4-digit year; S prefixes BC dates with a minus sign. | YYYY returns 2017 |
| Y YY YYY | 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 characters in the date format model.
- Punctuation such as hyphen (-), slash (/), comma (,), period (.) and colons (:)
- Character string literals are 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 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.
