Oracle7 Server SQL Reference

Format Models

A format model is a character literal that describes the format of DATE or NUMBER data stored in a character string. You can use a format model as an argument of the TO_CHAR or TO_DATE function for these purposes:

to specify the format for Oracle7 to use to return a value from the database to you

to specify the format for a value you have specified for Oracle7 to store in the database

Note that a format model does not change the internal representation of the value in the database.

This section describes how to use:

number format models

date format models

format model modifiers

Changing the Return Format

You can use a format model to specify the format for Oracle7 to use to return values from the database to you.

Example I

The following statement selects the commission values of the employees in department 30 and uses the TO_CHAR function to convert these commissions into character values with the format specified by the number format model '$9,990.99':

SELECT ename employee, TO_CHAR(comm,'$9,990.99') commission 
	FROM emp 
	WHERE deptno = 30 

EMPLOYEE   COMMISSION
---------- ----------
ALLEN         $300.00
WARD          $500.00
MARTIN      $1,400.00
BLAKE
TURNER          $0.00
JAMES

Because of this format model, Oracle7 returns the commissions with leading dollar signs, commas every three digits, and two decimal places. Note that the TO_CHAR function returns null for all employees with null in the COMM column.

Example II

The following statement selects the dates that each employee from department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':

SELECT ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate 
	FROM emp 
	WHERE deptno = 20 

ENAME      HIREDATE
---------- -----------------
SMITH      December 17, 1980
JONES      April 2, 1981
SCOTT      April 19, 1987
ADAMS      May 23, 1987
FORD       December 3, 1981

With this format model, Oracle7 returns the hire dates with the month spelled out, two digits for the day, and the century included in the year.

Supplying the Correct Format

You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column. When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. For example, a value that you insert into a DATE column must be a value of the DATE datatype or a character string in the default date format (Oracle7 implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, you must use the TO_DATE function to convert the value to the DATE datatype. You must also use a format model to specify the format of the character string.

Example III

The following statement updates JONES' hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1992 05 20' to a DATE value:

UPDATE emp 
	SET hiredate = TO_DATE('1992 05 20','YYYY MM DD') 
	WHERE ename = 'JONES'

Number Format Models

You can use number format models in these places:

in the TO_CHAR function to translate a value of NUMBER datatype to VARCHAR2 datatype

in the TO_NUMBER function to translate a value of CHAR or VARCHAR2 datatype to NUMBER datatype

All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).

Number Format Elements

A number format model is composed of one or more number format elements. Examples are shown in Table 3 - 17. Table 3 - 12 lists the elements of a number format model.

If a number format model does not contain the MI, S, or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space.

A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A number format model must not begin with a comma (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model.

Element Example Description
9 9999 Return value with the specified number of digits with a leading space if positive. Return value with the specified number of digits with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed point number.
0 0999 9990 Return leading zeros. Return trailing zeros.
$ $9999 Return value with a leading dollar sign.
B B9999 Return blanks for the integer part of a fixed point number when the integer part is zero (regardless of "0"s in the format model).
MI 9999MI Return negative value with a trailing minus sign "-". Returns positive value with a trailing blank.
S S9999 9999S Return negative value with a leading minus sign "-". Return positive value with a leading plus sign "+". Return negative value with a trailing minus sign "-". Return positive value with a trailing plus sign "+".
PR 9999PR Return negative value in <angle brackets>. Return positive value with a leading and trailing blank.
D 99D99 Return a decimal point (that is, a period ".") in the specified position.
G 9G999 Return a group separator in the position specified.
C C999 Return the ISO currency symbol in the specified position.
L L999 Return the local currency symbol in the specified position.
, (comma) 9,999 Return a comma in the specified position.
. (period) 99.99 Return a decimal point (that is, a period ".") int the specified position.
V 999V99 Return a value multiplied by 10ⁿ (and if necessary, round it up), where n is the number of "9"s after the "V".
EEEE 9.9EEEE Return a value using in scientific notation.
RN rn RN Return a value as Roman numerals in uppercase. Rerturn a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
FM FM90.9 Returns a value with no leading or trailing blanks.

Table 3 - 12. (continued) Number Format Elements

The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first of last position of a number format model.

The characters returned by some of these format elements are specified by initialization parameters. Table 3 - 13 lists these elements and parameters.

Element

Description

Initialization Parameter

Decimal character

NLS_NUMERIC_CHARACTER

Group separator

NLS_NUMERIC_CHARACTER

ISO currency symbol

NLS_ISO_CURRENCY

Local currency symbol

NLS_CURRENCY

Table 3 - 13. Number Format Element Values Determined by Initialization Parameters

The characters returned by these format elements can also be implicitly specified by the initialization parameter NLS_TERRITORY.

You can also change the characters returned by these format elements for your session with the ALTER SESSION command. For information on this command, see .

For information on these parameters, see Oracle7 Server Reference. You can also change the default date format for your session with the ALTER SESSION command. For information on this command, see .

Date Format Models

You can use date format models in the following places:

in the TO_CHAR function to translate a DATE value that is in a format other than the default date format

in the TO_DATE function to translate a character value that is in a format other than the default date format

Default Date Format

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY .

Maximum Length

The total length of a date format model cannot exceed 22 characters.

Date Format Elements

A date format model is composed of one or more date format elements as listed in Table 3 - 14. For input format models, format items cannot appear twice and also format items that represent similar information cannot be combined. For example, you cannot use 'SYYYY' and 'BC' in the same format string.

Element Meaning
- / , . ; : "text" Punctuation and quoted text is reproduced in the result.
AD A.D. AD indicator with or without periods.
AM A.M. Meridian indicator with or without periods.
BC B.C. BC indicator. with or without periods.
CC SCC Century; "S" prefixes BC dates with "-".
D Day of week (1-7).
DAY Name of day, padded with blanks to length of 9 characters.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
IW Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard.
HH HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
J Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers.
MI Minute (0-59).
MM Month (01-12; JAN = 01)
MONTH Name of month, padded with blanks to length of 9 characters.
MON Abbreviated name of month.
RM Roman numeral month (I-XII; JAN = I).
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1)
RR Last 2 digits of year; for years in other countries.
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
PM P.M. Meridian indicator with and without periods.
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
Y YYY Year with comma in this position.
YEAR SYEAR Year, spelled out; "S" prefixes BC dates with "-".
YYYY SYYYY 4-digit year; "S" prefixes BC dates with "-".
YYY YY Y Last 3, 2, or 1 digit(s) of year.

Table 3 - 14. (continued) Date Format Elements

Date Format Elements and National Language Support

The functionality of some date format elements depends on the country and language in which you are using Oracle7. For example, these date format elements return spelled values:

MONTH

BC or AD or B.C. or A.D.

AM or PM or A.M. or P.M.

The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE or implicitly with the initialization parameter NLS_LANGUAGE . The values returned by the YEAR and SYEAR date format elements are always in English.

The date format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

For information on these initialization parameters, see Oracle7 Server Reference.

ISO Standard Date Format Elements

Oracle7 calculates the values returned by the date format elements IYYY, IYY , IY , I , and IW according to the ISO standard. For information on the differences between these values and those returned by the date format elements YYYY, YYY, YY, Y, and WW, see the "National Language Support" chapter of Oracle7 Server Reference.

The RR Date Format Element

The RR date format element is similar to the YY date format element, but it provides additional flexibility for storing date values in other centuries. The RR date format element allows you to store twenty-first century dates in the twentieth century by specifying only the last two digits of the year. It will also allow you to store twentieth century dates in the twenty-first century in the same way if necessary.

If you use the TO_DATE function with the YY date format element, the date value returned is always in the current century. If you use the RR date format element instead, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Table 3 - 15 summarizes the behavior of the RR date format element.

If the specified two-digit year is

0 - 49

50 - 99

If the last two digits of the current year are:

0-49

The return date is in the current century.

The return date is in the century before the current one.

50-99

The return date is in the century after the current one.

The return date is in the current century.

Table 3 - 15. The RR Date Element Format

The following example demonstrates the behavior of the RR date format element.

Example IV

Assume these queries are issued before the year 2000:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') 
"4-digit year" 
	FROM DUAL 

4-digit year
------------
1995

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') 
"4-digit year" 
	FROM DUAL 

4-digit year
------------
2017

Assume these queries are issued in the year 2000 or after:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') 
"4-digit year" 
	FROM DUAL 

4-digit year
------------
1995 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') 
"4-digit year" 
	FROM DUAL 

4-digit year
------------
2017

Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values after the turn of the century.

Date Format Element Suffixes

Table 3 - 16 lists suffixes that can be added to date format elements:

Suffix

Meaning

Example Element

Example Value

Ordinal Number

DDTH

4TH

Spelled Number

DDSP

FOUR

SPTH or THSP

Spelled, ordinal number

DDSPTH

FOURTH

Table 3 - 16. Date Format Element Suffixes

When you add one of these suffixes to a date format element, the return value is always in English.

Note: Date suffixes are only valid on output and cannot be used to insert a date into the database.

Capitalization of Date Format Elements

Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.

Punctuation and Character Literals in Date Format Models

You can also include these characters in a date format model:

punctuation such as hyphens, slashes, commas, periods, and colons

character literals

These characters appear in the return value in the same location as they appear in the format model. Note that character literals must be enclosed in double quotation marks.

Format Model Modifiers

You can use the FM and FX modifiers in format models for the TO_CHAR function to control blank padding and exact format checking.

A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then re-enabled for the portion following its third, and so on.

FM "Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:

In a date format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading and trailing zeroes for subsequent number elements (such as MI) in a date format model. Since there is no blank padding, the length of the return value may vary. Without FM, the result of a character element is always right padded with blanks to a fixed length and the leading zero are always returned for a number element.

In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number in the result to right-justify it in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.

FX "Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE function:

Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model. Without FX, punctuation and quoted text in the character argument need only match the length and position of the corresponding parts of the format model.

The character argument cannot have extra blanks. Without FX, Oracle7 ignores extra blanks.

Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.

When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well. If any portion of the character argument violates any of these conditions, Oracle7 returns an error message.

Example V

Table 3 - 17 shows the results of the following query for different values of number and 'fmt':

SELECT TO_CHAR(number, 'fmt')
	FROM dual

number

'fmt'

Result

-1234567890

9999999999S

'1234567890-'

0

99.99

' 0.00'

+0.1

99.99

' .10'

-0.2

99.99

' -.20'

0

90.99

' 0.00'

+0.1

90.99

' .10'

-0.2

90.99

' -0.20'

0

9999

' 0'

1

9999

' 1'

0

B9999

' '

1

B9999

' 1'

0

B90.99

' '

+123.456

999.999

' 123.456'

-123.456

999.999

'-123.456'

+123.456

FM999.009

'123.456'

+123.456

9.9EEEE

' 1.2E+02'

+1E+123

9.9EEEE

' 1.0E+123'

+123.456

FM9.9EEEE

'1.23E+02'

+123.45

FM999.009

'123.45'

+123.0

FM999.009

'123.00'

+123.45

L999.99

' $123.45'

+123.45

FML99.99

'$123.45'

+1234567890

9999999999S

'1234567890+'

Table 3 - 17. Results of Example Number Conversions

Example VI

The following statement uses a date format model to return a character expression that contains the character literal "the" and a comma.

SELECT TO_CHAR(SYSDATE, 'fmDDTH "of" Month, YYYY') Ides 
	FROM DUAL 

Ides 
------------------ 
3RD of April, 1995

Note that the following statement also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:

SELECT TO_CHAR(SYSDATE, 'DDTH "of" Month, YYYY') Ides 
	FROM DUAL 

Ides

----------------------- 
03RD of April    , 1995

You can include a single quotation mark in the return value by placing two consecutive single quotation marks in the format model.

Example VII

The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:

SELECT TO_CHAR(SYSDATE, 'fmDay''"s Special"') Menu 
	FROM DUAL 

Menu 
----------------- 
Tuesday's Special

Two consecutive single quotation marks can also be used for the same purpose within a character literal in a format model.

Example VIII

Table 3 - 18 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX:

UPDATE table 
	SET date_column = TO_DATE(char, 'fmt')

char

'fmt'

Match or Error?

'15/ JAN /1993'

'DD-MON-YYYY'

Match

' 15! JAN % /1993'

'DD-MON-YYYY'

Match

'15/JAN/1993'

'FXDD-MON-YYYY'

Error

'15-JAN-1993'

'FXDD-MON-YYYY'

Match

'1-JAN-1993'

'FXDD-MON-YYYY'

Error

'01-JAN-1993'

'FXDD-MON-YYYY'

Error

'1-JAN-1993'

'FXFMDD-MON-YYYY'

Match

Table 3 - 18. Matching Character Data and Format Models with the FX Format Model Modifier

Element	Example	Description
9	9999	Return value with the specified number of digits with a leading space if positive. Return value with the specified number of digits with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed point number.
0	0999 9990	Return leading zeros. Return trailing zeros.
$	$9999	Return value with a leading dollar sign.
B	B9999	Return blanks for the integer part of a fixed point number when the integer part is zero (regardless of "0"s in the format model).
MI	9999MI	Return negative value with a trailing minus sign "-". Returns positive value with a trailing blank.
S	S9999 9999S	Return negative value with a leading minus sign "-". Return positive value with a leading plus sign "+". Return negative value with a trailing minus sign "-". Return positive value with a trailing plus sign "+".
PR	9999PR	Return negative value in <angle brackets>. Return positive value with a leading and trailing blank.
D	99D99	Return a decimal point (that is, a period ".") in the specified position.
G	9G999	Return a group separator in the position specified.
C	C999	Return the ISO currency symbol in the specified position.
L	L999	Return the local currency symbol in the specified position.
, (comma)	9,999	Return a comma in the specified position.
. (period)	99.99	Return a decimal point (that is, a period ".") int the specified position.
V	999V99	Return a value multiplied by 10ⁿ (and if necessary, round it up), where n is the number of "9"s after the "V".
EEEE	9.9EEEE	Return a value using in scientific notation.
RN rn	RN	Return a value as Roman numerals in uppercase. Rerturn a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
FM	FM90.9	Returns a value with no leading or trailing blanks.

Element	Meaning
`- / , . ; : "text"`	Punctuation and quoted text is reproduced in the result.
`AD A.D.`	AD indicator with or without periods.
`AM A.M.`	Meridian indicator with or without periods.
`BC B.C.`	BC indicator. with or without periods.
`CC SCC`	Century; "S" prefixes BC dates with "-".
`D`	Day of week (1-7).
`DAY`	Name of day, padded with blanks to length of 9 characters.
`DD`	Day of month (1-31).
`DDD`	Day of year (1-366).
`DY`	Abbreviated name of day.
`IW`	Week of year (1-52 or 1-53) based on the ISO standard.
`IYY IY I`	Last 3, 2, or 1 digit(s) of ISO year.
`IYYY`	4-digit year based on the ISO standard.
`HH HH12`	Hour of day (1-12).
`HH24`	Hour of day (0-23).
`J`	Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers.
`MI`	Minute (0-59).
`MM`	Month (01-12; JAN = 01)
`MONTH`	Name of month, padded with blanks to length of 9 characters.
`MON`	Abbreviated name of month.
`RM`	Roman numeral month (I-XII; JAN = I).
`Q`	Quarter of year (1, 2, 3, 4; JAN-MAR = 1)
`RR`	Last 2 digits of year; for years in other countries.
`WW`	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
`W`	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
`PM P.M.`	Meridian indicator with and without periods.
`SS`	Second (0-59).
`SSSSS`	Seconds past midnight (0-86399).
`Y YYY`	Year with comma in this position.
`YEAR SYEAR`	Year, spelled out; "S" prefixes BC dates with "-".
`YYYY SYYYY`	4-digit year; "S" prefixes BC dates with "-".
`YYY YY Y`	Last 3, 2, or 1 digit(s) of year.