Calender and Numeric Parameters

• First Day of the Week
• First Calendar Week of the Year
• Number of Days and Months in a Year
• First Year of Era

Some cultures consider Sunday to be the first day of the week. Others consider Monday to be the first day of the week. A German calendar starts with Monday.

First Calendar Week of the Year

Many countries, Germany, for example, use weeks for scheduling, planning, and bookkeeping. Oracle supports this convention.

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.

To support the ISO standard, the IW format element is provided that returns the ISO week number.

Number of Days and Months in a Year

Oracle supports six calendar systems in addition to the default Gregorian:

Japanese Imperial--uses the same number of months and days as Gregorian, but the year starts with the beginning of each Imperial Era

ROC Official--uses the same number of months and days as Gregorian, but the year starts with the founding of the Republic of China

Persian--has 12 months of equal length

Thai Buddha--uses a Buddhist calendar

Arabic Hijrah--has 12 months with 354 or 355 days

English Hijrah--has 12 months with 354 or 355 days

First Year of Era

The Islamic calendar starts from the year of the Hegira. The Japanese Imperial calendar starts from the beginning of an Emperor's reign. For example, 1998 is the tenth year of the Heisei era. It should be noted, however, that the Gregorian system is also widely understood in Japan, so both 98 and Heisei 10 can be used to represent 1998.

NLS_CALENDAR

Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Gregorian
Range of values: Any valid calendar format name

Many different calendar systems are in use throughout the world. NLS_CALENDAR specifies which calendar system Oracle uses.

NLS_CALENDAR can have one of the following values:

• Arabic Hijrah
• English Hijrah
• Gregorian
• Japanese Imperial
• Persian
• ROC Official (Republic of China)
• Thai Buddha

SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15

Numeric Parameters

Numeric Formats

The database must know the number-formatting convention used in each session to interpret numeric strings correctly. For example, the database needs to know whether numbers are entered with a period or a comma as the decimal character (234.00 or 234,00). Similarly, the application needs to be able to display numeric information in the format expected at the client site.

NLS_NUMERIC_CHARACTERS

Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and ALTER SESSION
Default value: Default decimal character and group separator for a particular territory
Range of values: Any two valid numeric characters

This parameter specifies the decimal character and grouping separator, overriding those defined implicitly by NLS_TERRITORY. The group separator is the character that separates integer groups (that is, the thousands, millions, billions, and so on). The decimal character separates the integer and decimal parts of a number.

Any character can be the decimal or group separator. The two characters specified must be single-byte, and both characters must be different from each other. The characters cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>).

The characters are specified in the following format:

NLS_NUMERIC_CHARACTERS = ""

The grouping separator is the character returned by the number format mask G. For example, to set the decimal character to a comma and the grouping separator to a period, the parameter should be set as follows:

NLS_NUMERIC_CHARACTERS = ",."

Both characters are single byte and must be different. Either can be a space.

SQL statements can include numbers represented as numeric or text literals. Numeric literals are not enclosed in quotes. They are part of the SQL language syntax and always use a dot as the decimal separator and never contain a group separator. Text literals are enclosed in single quotes. They are implicitly or explicitly converted to numbers, if required, according to the current NLS settings. For example, in the following statement:

INSERT INTO SIZES (ITEMID, WIDTH, HEIGHT, QUANTITY)
VALUES (618, '45,5', 27.86, TO_NUMBER('1.234','9G999'));

618 and 27.86 are numeric literals. The text literal '45,5' is implicitly converted to the number 45.5 (assuming that WIDTH is a NUMBER column). The text literal '1.234' is explicitly converted to a number 1234. This statement is valid only if NLS_NUMERIC_CHARACTERS is set to ",.".

You can alter the default value of NLS_NUMERIC_CHARACTERS in either of these ways:

• Change the value of NLS_NUMERIC_CHARACTERS in the initialization parameter file and then restart the instance.
• Use the ALTER SESSION SET NLS_NUMERIC_CHARACTERS statement to change the parameter's value during a session.