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.
Even with this flexibility, the database still supports only a single default date format in a given
instance. Both developers and users must be aware of this format when working with dates. Later
sections of this chapter explore approaches in PL/SQL that give the user much more flexibility when
entering dates in their applications.
As you can see, format masks (such as MMDDYY and Month DD, YYYY) play an important role in
the conversion of date and character data.
Table 14.2 provides the full set of date format masks and
explains how to use them in all their variations. You can use the format elements in any combination,
in any order. You can even use the same format element more than once in your format mask.
Following the table are examples showing these variations.
Table 14.2: Date Format Model Elements
Mask Description
SCC or CC The century. If the SCC format is used, any B.C. dates are
prefaced with a hyphen (-).
SYYYY or YYYY The four-digit year. If the SYYYY format is used, any B.C.
dates are prefaced with a hyphen (-).
IYYY The four-digit ISO standard year.
YYY or YY or Y The last three, two, or one digits of the year. The current century
is the default.
IYY or IY or I The last three, two, or one digits of the ISO standard year. The
current century is the default.
Y,YYY The four-digit year with a comma.
SYEAR or YEAR or SYear or
Year
The year spelled out. The S prefix places a negative sign in front
of B.C. dates.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
RR The last two digits of the year. This format is used to display

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.
HH or HH12 The hour in the day, from 1 through 12.
HH24 The hour in the day, from 0 through 23.
MI The minutes component of the date's time, from 0 through 59.
SS The seconds component of the date's time, from 0 through 59.
SSSSS The number of seconds since midnight of the time component.
Values range from 1 through 86399, with each hour comprising
3600 seconds.
TH Suffix which converts a number to its ordinal format; for
example, 4 becomes 4th and 1 becomes 1st. This element can
appear only at the end of the entire format mask. The return
value is always in English, regardless of the date language.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SP Suffix which converts a number to its spelled format; for
example, 4 becomes FOUR, 1 becomes ONE, and 221 becomes
TWO HUNDRED TWENTY-ONE. This element can appear
only at the end of the entire format mask. The return value is
always in English, regardless of the date language.
SPTH Suffix which converts a number to its spelled and ordinal
format; for example, 4 becomes FOURTH and 1 becomes
FIRST. This element can appear only at the end of the entire
format mask. The return value is always in English, regardless
of the date language.
FX Element which requires exact pattern matching between data
and format model. (FX stands for Format eXact.)
FM Element which toggles suppression of blanks in output from
conversion. (FM stands for Fill Mode.)
Other text

complete format mask. Format elements with a description starting with "Suffix:" can be used only at
the end of the complete format mask.
Table 14.3: Number Format Model Elements
Format Elements Description
9 Each 9 represents a significant digit to be returned. Leading zeros in a number
are displayed or treated as blanks.
0 Each represents a significant digit to be returned. Leading zeros in a number
are displayed or treated as zeros.
$ Prefix: puts a dollar sign in front of the number.
B Prefix: returns a zero value as blanks, even if the format element was used to
show a leading zero.
MI
Suffix: places a minus sign (-) after the number if it is negative. For positive
values it returns a trailing space, which is different from NULL.
S
Prefix: places a plus sign (+) in front of a positive number and a minus sign
(-) before a negative number.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PR
Suffix: places angle brackets (< and >) around a negative value. For positive
values it places leading and trailing spaces around the number.
D Specifies the location of the decimal point in the returned value. All format
elements to the left of the D will format the integer component of the value.
All format elements to the right of the D will format the fractional part of the
value. The character used for the decimal character is determined by the
database initialization parameter NLS_NUMERIC_CHARACTERS.
G Specifies the location of the group separator (for example, a comma to
separate thousands as in 6,734) in the returned value. The character used for
the group separator is determined by the database initialization parameter
NLS_NUMERIC_CHARACTERS.

Descriptions
Book Index
14.2 Conversion Function
Descriptions
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 14.1 Conversion
Formats
Chapter 14
Conversion Functions
Next: 14.3 Conversion
Function Examples

14.2 Conversion Function Descriptions
This section describes the various conversion functions provided by PL/SQL.
14.2.1 The CHARTOROWID function
The CHARTOROWID function converts a string of either type CHAR or VARCHAR2 to a value of
type ROWID. The specification of the CHARTOROWID function is:
FUNCTION CHARTOROWID (string_in IN CHAR) RETURN ROWID
FUNCTION CHARTOROWID (string_in IN VARCHAR2) RETURN ROWID
In order for CHARTOROWID to successfully convert the string, it must be of the format:
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the number of the block in the database file, RRRR is the number of the row in
the block, and FFFF is the number of the database file. All three numbers must be in hexadecimal
format.
If the input string does not conform to the above format, PL/SQL raises the VALUE_ERROR
exception.

FUNCTION ROWIDTOCHAR (row_in IN ROWID ) RETURN VARCHAR2
The string returned by this function has the format:
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the number of the block in the database file, RRRR is the number of the row in
the block, and FFFF is the number of the database file. All three numbers are in hexadecimal format.
14.2.6 The TO_CHAR function (date conversion)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The TO_CHAR function can be used to convert both dates and numbers to a variable-length string.
The following specification describes TO_CHAR for dates:
FUNCTION TO_CHAR
(date_in IN DATE
[, format_mask IN VARCHAR2
[, nls_language IN VARCHAR2]])
RETURN VARCHAR2
where date_in is the date to be converted to character format, the format_mask is the mask made up
of one or more of the date format elements, and nls_language is a string specifying a date language.
Both the format mask and the NLS language parameters are optional.
If the format mask is not specified, then the default date format for the database instance is used. This
format is DD-MON-YY, unless the initialization parameter NLS_DATE_FORMAT is included in
the initialization file. The format of the specification of an alternative date mask is:
NLS_DATE_FORMAT = 'MM/DD/YYYY'
If the NLS language parameter is not specified, then the default date language for the instance is
used. This is either the language for the instance specified by the NLS_LANGUAGE parameter, or
the date language specified in the initialization file with the parameter NLS_DATE_LANGUAGE.
Note that if you want to specify a date language, you also must include a format mask. You cannot
skip over the intervening parameters.
Here are some examples of TO_CHAR for date conversion:
●
Notice that there are two blanks between month and day and a leading zero for the fifth day:
TO_CHAR (SYSDATE, 'Month DD, YYYY') ==> 'February

where number_in is the number to be converted to character format, the format_mask is the mask
made up of one of more of the number format elements, and nls_language is a string specifying one
or more of the NLS parameters which affect the way numbers are displayed. Both the format mask
and the NLS language parameters are optional.
If the format mask is not specified, then the default number format for the database instance is used.
Here are some examples of TO_CHAR for number conversion:
TO_CHAR (564.70, '$999.9') ==> $564.7
TO_CHAR (564.70, '$0000999.9') ==> $0000564.7
14.2.8 The TO_DATE function
The TO_DATE function converts a character string to a true DATE datatype. The specification of the
TO_DATE function is overloaded for string and number input:
FUNCTION TO_DATE (string_in IN VARCHAR2
[, format_mask IN VARCHAR2
[, nls_language IN VARCHAR2 ]]
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
)
RETURN DATE;
FUNCTION TO_DATE
(number_in IN NUMBER
[, format_mask IN VARCHAR2 [, nls_language IN
VARCHAR2 ]])
RETURN DATE;
The second version of TO_DATE can be used only with the format mask of J for Julian date. The
Julian date is the number of days which have passed since January 1, 4712 B.C. Only in this use of
TO_DATE can a number be passed as the first parameter of TO_DATE.
For all other cases, string_in is the string variable, literal, named constant, or expression to be
converted, format_mask is the format mask TO_DATE will use to convert the string, and
nls_language is a string which specifies the language which is to be used to interpret the names and
abbreviations of both months and days in the string. The format of nls_language is as follows:
'NLS_DATE_LANGUAGE=<language>'

The TO_NUMBER function converts both fixed- and variable-length strings to numbers using the
associated format mask. The specification of the TO_NUMBER function is as follows:
FUNCTION TO_NUMBER
(string_in IN CHAR
[, format_mask VARCHAR2 [, nls_language VARCHAR2 ]])
RETURN NUMBER;
FUNCTION TO_NUMBER
(string_in IN VARCHAR2
[, format_mask VARCHAR2 [, nls_language VARCHAR2 ]])
RETURN NUMBER;
where string_in is the string containing a sequence of characters to be converted to a number,
format_mask is the optional string directing TO_NUMBER how to convert the character bytes to a
number, and nls_language is a string containing up to three specifications of National Language
Support parameters, as follows:
NLS_NUMERIC_CHARACTERS
The characters used to specify the decimal point and the group separator in a number. The
decimal point character for the American language is a dot (.) while the group separator is a
comma (,).
NLS_CURRENCY
The character(s) used to specify the local currency symbol. The currency character for the
American language is a dollar sign ($).
NLS_ISO_CURRENCY
The character(s) used to specify the international currency symbol in the string.
The format for nls_language in the call to TO_NUMBER is as follows:
'NLS_NUMERIC_CHARACTERS = ''string'''
'NLS_CURRENCY = ''string'''
'NLS_ISO_CURRENCY = ''string'''
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Two contiguous single quotes are needed before and after the values for each string value so that PL/
SQL will parse the entire parameter and leave behind a single quote around each value.

1994'
With the FM modifier at the beginning of the format mask, however, both the extra blank and the
leading zeros disappear:
TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') ==> April 5, 1994'
The modifier can be specified in upper-, lower-, or mixed-case; the effect is the same.
The FM modifier is a toggle, and can appear more than once in a format model. Each time it appears
in the format, it changes the effect of the modifier. By default (that is, if FM is not specified
anywhere in a format mask), blanks are not suppressed and leading zeros are included in the result
value. So the first time that FM appears in the format it indicates that blanks and leading zeros are
suppressed for any following elements. The second time that FM appears in the format, it indicates
that blanks and leading zeros are not suppressed for any following elements, and so on.
In the following example I suppress the padded blank at the end of the month name, but preserve the
leading zero on the day number with a second specification of FM:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
TO_CHAR (SYSDATE, 'fmMonth FMDD, YYYY') ==> April 05,
1994'
If you do not use FM in your mask, a converted date value is always right-padded with blanks to a
fixed length (that length is dependent on the different format elements you use). When you do use
FM, on the other hand, the length of your return value may vary depending on the actual values
returned by the different format elements.
When you do not use FM to convert a number to a character string, the resulting value is always left-
padded with blanks so that the number is right-justified to the length specified by the format (or
declaration of the variable). When you do use FM, the left-padded blanks are suppressed and the
resulting value is left-justified.
Here are some examples of the impact of FM on numbers converted with TO_CHAR:
TO_CHAR (8889.77, 'L9999D99') ==> ' $8889.77'
TO_CHAR (8889.77, 'fmL9999D99') ==> '$8889.77'
The FM modifier can also be used in the format model of a call to the TO_DATE function to fill a
string with blanks or zeros to match the format model. This variation of FM is explored in the
discussion of FX.

from entering data in a nonstandard format. In some cases, it simply is not a reflection of everything
being OK when a date string has a pound sign (#) instead of a hyphen (-) between the day and month
numbers. For these situations, you can use the FX modifier to enforce an exact match between string
and format model.
With FX, there is no flexibility for interpretation of the string. It cannot have extra blanks if none are
found in the model. Its numeric values must include leading zeros if the format model specifies
additional digits. And the punctuation and literals must exactly match the punctuation and quoted text
of the format mask (except for case, which is always ignored). In all of the following examples, PL/
SQL raises one of the following errors:
ORA-01861: literal does not match format string
ORA-01862: wrong number of digits for this format item
TO_DATE ('Jan 15 1994', 'fxMON DD YYYY')
TO_DATE ('1-1-4', 'fxDD-MM-YYYY')
TO_DATE ('7/16/94', 'FXMM/DD/YY')
TO_DATE ('JANUARY^1^ the year of 94', 'FXMonth-
dd-"WhatIsaynotdo"yy')
The FX modifier can be specified in upper-, lower-, or mixed-case; the effect is the same.
The FX modifier is a toggle, and can appear more than once in a format model. Each time it appears
in the format, it changes the effect of the modifier. By default (that is, if FX is not specified anywhere
in a format mask), an exact match is not required in any part of the string (as described above). So the
first time that FX appears in the format it turns on exact matching for any following elements. The
second time that FX appears in the format it indicates that an exact match is not required for any
following elements, and so on.
In the following example I specify FX three times. As a result, an exact match is required for the day
number and the year number, but not the month number:
TO_DATE ('07-1-1994', 'FXDD-FXMM-FXYYYY') ==> 07-JUL-1994
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This next attempt at date conversion will raise ORA-01862 because the year number is not fully
specified:
TO_DATE ('07-1-94', 'FXDD-FXMM-FXYYYY') -- Invalid string

is how RR works:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If the current year is in the first half of the century (years through 49) then:
●
If you enter a date in the first half of the century, RR returns the current century.
●
On the other hand, if you enter a date in the latter half of the century, RR returns the previous
century.
●
If the current year is in the latter half of the century (years 50 through 99) then:
❍
If you enter a date in the first half of the century, RR returns the next century.
❍
If you enter a date in the latter half of the century, RR returns the current century.
Here are some examples of the impact of RR. Notice that the same year numbers are returned for
Year 88 and Year 18, even though SYSDATE returns a current date in the 20th and 21st centuries,
respectively:
SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date",
TO_CHAR (TO_DATE ('14-OCT-88', 'DD-MON-RR'),
'YYYY') "Year 88",
TO_CHAR (TO_DATE ('14-OCT-18', 'DD-MON-RR'),
'YYYY') "Year 18"
FROM dual;
Current Date Year 88 Year 18
------------ ------- -------
11/14/1994 1988 2018
SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date",
TO_CHAR (TO_DATE ('10/14/88', 'MM/DD/RR'), 'YYYY')
"Year 88",
TO_CHAR (TO_DATE ('10/14/18', 'MM/DD/RR'), 'YYYY')

end_date
where criteria is the name of the block containing the start_date and end_date fields. The colons (:) in
front of the field names indicate to PL/SQL that these are bind variables from the host environment.
Sometimes this general logic can be passed directly to the SQL layer. In other situations,
programmers must use the Pre-Query trigger or the SET_BLOCK_PROPERTY built-in to alter the
SQL statement directly. In this case, they will need to create a string date range from the input dates.
Rather than write the application-specific code to handle this each time, you can build a generic
utility, using TO_CHAR and TO_DATE conversion functions.
I offer below the date_range function. Its specification is as follows:
FUNCTION date_range
(start_date_in IN DATE,
end_date_in IN DATE,
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.