Date Format Models
In Versions 6 and earlier of the Oracle RDBMS, the default format for dates as character values was DD-MON-YY, a cause of consternation for many developers and users. While this format is common in many parts of the world, very few people use it in the United States.
Beginning with Oracle7, your database administrator can specify your own default date format (which takes effect on the initialization or startup of the RDBMS instance) with the NLS_DATE_FORMAT parameter as follows:
NLS_DATE_FORMAT = 'MM/DD/YYYY'The default date format is also set implicitly with another initialization parameter, NLS_ TERRITORY. When you specify an NLS_TERRITORY value, you set conventions for date format, date language, numeric formats, currency symbols, and week start day.
You also have the option of specifying a date format at the session level, a capability that can come in handy if your particular needs differ from those of the majority of database users. Use the ALTER SESSION command to specify a session-level default date format. The following example works in Oracle8i or higher, and sets the default date format to MM/DD/YYYY:
To check the default date format in effect for your session at any given time, issue the following query against the NLS_SESSION_PARAMETERS data dictionary view:
SELECT valueFROM nls_session_parametersWHERE parameter='NLS_DATE_FORMAT';Regardless of how the default date format is set, both developers and users must be aware of this format when working with dates. In the upcoming sections Section 10.2.2 and Section 10.2.3, you'll learn how to specify date format models for individual conversions rather than relying on the database or session default model.
As you can see, format models (such as MMDDYY and Month DD, YYYY) play an important role in the conversion of date and character data. Date format models are made up of elements. For example, the elements in the MM/DD/YYYY model are:
MM
DD
YYYY
The two forward-slash (/) characters.
Table 10-1 shows the full set of date format elements and explains how to use them in all their variations. Following the table are examples showing some of these variations. You can use the format elements in any combination, in any order. However, when specifying NLS_DATE_FORMAT, you cannot specify the same date element twice; for example, you can specify only one of MONTH, MON, and MM because all three refer to the month.
|
Some elements in Table 10-1 apply only when translating datetime values from Oracle's internal format into character strings, and not vice versa. Such elements can't be used in a default date model (i.e., with NLS_DATE_FORMAT) because the default date model applies to conversions in both directions. These elements are noted as "Output-only" in the table.
| Table 10-1. Date format model elements | |
| Element | Description |
| SCC or CC | The century. If the SCC format is used, any B.C. dates are prefaced with a minus sign (-). Output-only. |
| SYYYY or YYYY | The four-digit year. If the SYYYY format is used, any B.C. dates are prefaced with a minus sign (-). |
| IYYY | The four-digit ISO standard year. Output-only. |
| YYY or YY or Y | The last three, two, or one digits of the year. The current century is the default when using these elements to convert a character string value into a date. |
| IYY or IY or I | The last three, two, or one digits of the ISO standard year. Output-only. |
| Y,YYY | The four-digit year with a comma. |
| SYEAR, YEAR, SYear, Year, syear, or year | The year spelled out in words (e.g., "two thousand two"). The S prefix places a negative sign in front of B.C. dates. The format may be upper-, mixed-, or lowercase. Output-only. |
| RR | The last two digits of the year. This format is used to display years in centuries other than our own. See Section 10.2.2.6. |
| RRRR | Same as RR when used for output; accepts four-digit years when used for input. |
| BC or AD | The B.C. or A.D. indicator, without periods. |
| B.C. or A.D. | The B.C. or A.D. indicator, with periods. |
| E | The abbreviated era name. Valid only for the following calendars: Japanese Imperial, ROC Official, and Thai Buddha. Input-only. |
| EE | The full era name. |
| Q | The quarter of the year, from 1 through 4. January through March are in the first quarter, April through June in the second quarter, etc. Output-only. |
| MM | The number of the month in the year, from 01 through 12. January is month number 01, September is 09, etc. |
| RM | The Roman numeral representation of the month number, from I through XII. January is I, September is IX, etc. |
| MONTH, Month, or month | The name of the month, in upper-, mixed-, or lowercase format. |
| MON, Mon, or mon | The abbreviated name of the month, as in JAN for January. This also may be in upper-, mixed-, or lowercase format. |
| WW | The week of the year, from 1 through 53. Output-only. |
| IW | The week of the year, from 1 through 52 or 1 through 53, based on the ISO standard. Output-only. |
| W | The week of the month, from 1 through 5. Week 1 starts on the first day of the month and ends on the seventh. Output-only. |
| DDD | The day of the year, from 1 through 366. |
| DD | The day of the month, from 1 through 31. |
| D | The day of the week, from 1 through 7. The day of the week that is decreed the first day is specified implicitly by the NLS_TERRITORY initialization parameter for the database instance. |
| DAY, Day, or day | The name of the day in upper-, mixed, or lowercase format. |
| DY, Dy, or dy | The abbreviated name of the day, as in TUE for Tuesday. |
| J | The Julian day format of the date (counted as the number of days since January 1, 4712 B.C., the earliest date supported by the Oracle RDBMS). |
| AM or PM | The meridian indicator (morning or evening) without periods. |
| A.M. or P.M. | The meridian indicator (morning or evening) with periods. |
| TZD | The abbreviated time zone name; for example, EST, PST, etc. This is an input-only format, which may seem odd at first. |
| TZH | The time zone hour displacement. For example, -5 indicates a time zone five hours earlier than UTC. |
| TZM | The time zone minute displacement. For example, -5:30 indicates a time zone that is five hours, thirty minutes earlier than UTC. A few such time zones do exist. |
| TZR | The time zone region. For example, "US/Eastern" is the region in which EST (Eastern Standard Time) and EDT (Eastern Daylight Time) are valid. |
| HH or HH12 | The hour of the day, from 1 through 12. HH12 is output-only. |
| HH24 | The hour of the day, from 0 through 23. |
| MI | The minutes component of the datetime value, from 0 through 59. |
| SS | The seconds component of the datetime value, from 0 through 59. |
| SSSSS | The number of seconds since midnight of the time component. Values range from 0 through 86399, with each hour comprising 3600 seconds. |
| FF | The fractional seconds. Only valid when used with TIMESTAMP values. Always use FF (two Fs) regardless of the number of decimal digits you wish to see or use. Any other number of Fs is invalid. |
| X | The local radix character. In American English, this is a period (.). This element can be placed in front of FF so that fractional seconds are properly interpreted and represented. |
| TH | Suffix that converts a number to its ordinal format; for example, 4 becomes 4th and 1 becomes 1st. This element can appear at the end of any element that results in a number. For example, "DDth-Mon-YYYY' results in output such as "15th-Nov-1961". The return value is always in English, regardless of the date language. |
| SP | Suffix that converts a number to its spelled format. This element can appear at the end of any element that results in a number. For example, a mask such as "DDth-Mon-Yyyysp" results in output such as "15th-Nov-One Thousand Nine Hundred Sixty-One". The return value is always in English, regardless of the date language. (Note that Yyyy resulted in mixed-case words). |
| SPTH | Suffix that converts a number to its spelled and ordinal format; for example, 4 becomes FOURTH and 1 becomes FIRST. This element can appear at the end of any element that results in a number. For example, a mask such as "Ddspth Mon, Yyyysp" results in output such as "Fifteenth Nov, One Thousand Nine Hundred Sixty-One". The return value is always in English, regardless of the date language. |
| FX | Element that requires exact pattern matching between data and format model. (FX stands for Format eXact.) See Section 10.2.2.5 later in this chapter. |
| FM | Element that toggles suppression of blanks in output from conversion. (FM stands for Fill Mode.) See Section 10.2.3.3 later in this chapter. |
| Other text | Any punctuation, such as a comma (,) or slash (/) or hyphen (-), will be reproduced in the formatted output of the conversion. You can also include text within double quotes (" ") and the text will be represented as entered in the converted value. See the examples in Section 10.2.3.1 for an illustration of this element. |
Note that whenever a date format returns a spelled value (words rather than numbers, as with MONTH, MON, DAY, DY, AM, and PM), the language used to spell these words is determined by the National Language Support parameters, NLS_DATE_LANGUAGE and NLS_LANGUAGE, or by the optional date language argument you can pass to both TO_ CHAR and TO_DATE.
Here are some examples of date format masks composed of the above format elements:
'Month DD, YYYY''MM/DD/YY Day A.M.''Year Month Day HH24:MI:SS''J''SSSSS-YYYY-MM-DD''"A beautiful summer morning on the" DDth" day of "Month'See the description of the TO_CHAR and TO_DATE functions for more examples of the use and resulting values of these masks.
| ISO Dates The IYY and IW elements represent the ISO (International Standards Organization) year and week. The ISO calendar is a good example of "design by committee." The first day of the ISO year is always a Monday and is determined by the following rules: · When January 1 falls on a Monday, the ISO year begins on the same day. · When January 1 falls on a Tuesday through Thursday, the ISO year begins on the preceding Monday. · When January 1 falls on a Friday through Sunday, the ISO year begins on the following Monday. These rules lead to some strange situations. For example, 31-Dec-2001 is considered to be the first day of ISO year 2002, and if you display that date using the IYYY format, 31-Dec-2002 is exactly what you'll get. ISO weeks always begin on Mondays and are numbered from the first Monday of the ISO year. |