Oracle7 Server Reference Manual
Date and Number Formats
Several format masks are provided with the TO_CHAR, TO_DATE, and TO_NUMBER functions to format dates and numbers according to the relevant conventions.
Note: The TO_NUMBER function also accepts a format mask.
Date Formats
A format element RM (Roman Month) returns a month as a Roman numeral. Both uppercase and lowercase can be specified, using RM and rm respectively. For example, for the date 7 Sep 1992, "DD-rm-YY" will return "07-ix-92" and "DD-RM-YY" will return "07-IX-92".
Note that the MON and DY format masks explicitly support month and day abbreviations that may not be three characters in length. For example, the abbreviations "Lu" and "Ma" can be specified for the French "Lundi" and "Mardi", respectively.
Week and Day Number Conventions
The week numbers returned by the WW format mask are calculated according to the algorithm int((day-ijan1)/7). This week number algorithm does not follow the ISO standard (2015, 1992-06-15).
To support the ISO standard, a format element IW is provided that returns the ISO week number. In addition, format elements I IY IYY and IYYY, equivalent in behavior to the format elements Y YY YYY YYYY, return the year relating to the ISO week number.
In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.
- If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.
- If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.
For example, January 1, 1991, is a Tuesday, so Monday, December 31, 1990, to Sunday, January 6, 1991, is week 1. Thus the ISO week number and year for December 31, 1990, is 1, 1991. To get the ISO week number, use the format mask "IW" for the week number and one of the "IY" formats for the year.
Number Formats
Several additional format elements are provided for formatting numbers:
- D (Decimal) returns the decimal character
- G (Group) returns the group separator
- L (Local currency) returns the local currency symbol
- C (international Currency) returns the international currency symbol
- RN (Roman Numeral) returns the number as its Roman numeral equivalent
For Roman numerals, both uppercase and lowercase can be specified, using RN and rn, respectively. The number to be converted must be an integer in the range 1 to 3999.
For a more information on using date masks, see Oracle7 Server SQL Reference.