Dates in Oracle Applications
There are two main
ways that dates are stored in the applications: as character strings or as
binary, Julian dates. Dates, both as character strings and as Julian dates, are
used in various places in the applications, including database tables, C and
Pro*C code, PL/SQL versions 1, 2, and 8, concurrent programs, Oracle Reports,
Java code, flexfield columns, form fields, and profile values.
Before continuing the
discussion of how dates are used in Oracle Applications, it is helpful to
establish some definitions.
Positive and Negative Infinity Dates
Positive and negative
infinity dates are used in code as comparison values. They are meant as dates
that are not reasonable valid dates in the life of the code.
Oracle Applications
use January 1, 9999 as positive infinity and January 1, 1000 as negative
infinity wherever four-digit year support is provided.
Common incorrect
choices for positive infinity in custom code include September 9, 1999 and
December 31, 1999.
Format Mask
The format mask
determines how the date is displayed or stored. Format masks specify how to
represent the day, month, year and time of a date value. For example, the date
March 11, 1999 can be represented as 11-MAR-1999, 03/11/1999, or as 1999/03/11.
A default format mask
variable (NLS_DATE_FORMAT) determines the format mask
unless a different
mask is explicitly set. Oracle Applications sets the
NLS_DATE_FORMAT to be
DD-MON-RR.
Canonical Date Format
When dates are stored
in a character format, one standard format, called the canonical date format,
is used to prevent confusion and inconsistencies.
Oracle Applications
uses YYYY/MM/DD HH24:MI:SS (the time portion is optional) as the canonical date
format whenever dates are represented by a character string. This format is
independent of the user's language, and preserves the sort order of the dates.
Oracle Dates and Julian Dates
Oracle
dates (OraDates) include a range from January 1, 4712 BC to December 31, 4712
AD. They are represented as seven byte binary digits, often referred to as
Julian Dates.
Oracle dates have a
span of 3,442,447 days. Thus, January 1, 4712 BC is Julian day 1, and December
31, 4712 AD is Julian day 3,442,447. January 1, 1 AD is Julian day 1,721,424.
Oracle dates include the year, month, day and time.
The Oracle database
uses Oracle dates in its date columns, and wherever dates are stored using the
DATE data type. Storing dates in this binary format is usually the best choice,
since it provides year 2000 compliance and the ability to easily format dates
in any style.
Oracle dates are used
in SQL statements, PL/SQL code, and Pro*C code. Pro*C code uses Oracle dates by
binding binary arrays as data type 12. Oracle dates are never seen by users;
the format is intended for internal use, not for display.
The Oracle
Applications do not support BC dates, so dates before Julian 1,721,424 are not
used.
Explicit Format Mask
Date values in the
applications must frequently be converted from a Julian date to a character
string, or from a string to a Julian date for storing in a date-type column or
field. For example, the functions TO_DATE and TO_CHAR perform these conversions
in both SQL and PL/SQL.
When dates are
converted into a character string in SQL or PL/SQL, a format mask can be
explicitly included:
to_char(my_date,'YYYY/MM/DD')
If the developer does
not specify a format mask, the system uses a default, implicit format mask.
When converting a
date-type value, always explicitly state the format desired. This ensures that
the correct date format is used and that context-sensitive variables do not
cause your conversion to fail.
When you use a PL/SQL
variable to hold the value from an Oracle Forms DATE or DATETIME field, you can
access that value using the function NAME_IN as shown in the example below:
x_date_example
:= TO_DATE(NAME_IN('block.datetime_field'), 'DD-MON-YYYY
HH24:MI:SS');
The NAME_IN function
returns all values as CHAR. Thus when dealing with a DATE field, you must
explicitly supply a mask to convert from a DATE format to a CHAR. However,
Oracle Forms has an internal representation and a displayed representation for
dates. When you use NAME_IN, it is accessing the internal representation.
Furthermore, Oracle Forms only uses the following masks when accessing dates
with NAME_IN:
DATE fields:
|
DD-MON-YYYY
|
DATETIME fields:
|
DD-MON-YYYY HH24:MI:SS
|
This mask is used
internally only to convert from DATE to CHAR; it is not affected by, nor does
it affect, what the user sees. For this reason, there is not an issue
concerning what date mask to use if translation is a concern.
If a DATE field has a
mask of MM/DD/YYYY, causing the user to see something like 2/13/1995,
internally you still access it with the mask DD-MON-YYYY. You will typically
assign it to a DATE variable, so the internal mask does not cause a concern.
If you intend to
assign a DATE field to a CHAR variable and manipulate it as a CHAR, then you
may have a translation issue. In that case, you should first assign it to a
DATE variable, then assign it to the CHAR variable with a translatable mask
such as
DD/MM/YYYY.
Implicit Format Mask
If a conversion from a
date-type value to a character string is done without explicitly stating the
format mask desired, an implicit format mask is applied. This implicit format
mask is determined by environment settings such as NLS_DATE_FORMAT.
to_char(my_date)
Oracle Application
standards require an explicit format mask.
NLS_DATE_FORMAT Variable
This environment
variable usually determines the implicit date format. Oracle tools
typically use the
NLS_DATE_FORMAT to validate, display, and print dates. In all of these cases
you can and should provide an overriding value by explicitly defining the
format mask.
OraDates
and Binary Dates
OraDates and binary
dates are encoded using Julian dates.
Flexible Date Formats
Oracle Applications
provides flexible date support: the ability to view dates in forms in the
user's preferred format. Flexible date format is the ability to display dates
in the way expected by a user, usually based on the user's language and
territory. There are several different formats used around the world in which
to view dates. Some countries use DD-MON-YYYY, other locations use DD/MM/YYYY.
Oracle Applications also gives you the ability to use dates in a multilingual
environment.
If the applications
are running multilingually, then two users of the applications may expect
different formats for the date values. Flexible dates display the date value
correctly for both users.
Date Coding Standards
There are several
principles of coding with dates that are applied wherever dates are used by the
Oracle Applications. All new code should follow these standards.
•
All
treatments of date values as strings in database tables use a canonical form
which handles full four-digit years and is independent of language and display
and input format. The recommended form is YYYY/MM/DD (plus, optionally, the
time as HH24:MI:SS). Dates stored in this form are converted to the correct
external format whenever they are displayed or received from users or other
programs.
•
No
generic processing logic, including Pro*C code, PL/SQL code, and SQL statements
of all kinds (including statements stored in the database), should hardcode
either assumptions about the date format or unconverted date literals.
All treatments of
dates as strings should use explicit format masks which contain the full year
(four-digit years) and are language-independent. The recommended treatment is
either as a Julian date (format = 'J') or, if the date must be in character
format, using the canonical format YYYY/MM/DD.
•
Standard
positive and negative infinity dates are 9999/01/01 and 1000/01/01.
•
Never
use B.C. dates.
•
When
it is necessary to hardcode a date, avoid language-specific months. Instead,
use a Julian date and specify full century information:
my_date =
to_date('9999/01/01','YYYY/MM/DD')
Using Dates While Developing Application
Forms
NLS_DATE_FORMAT
Oracle tools (with
some exceptions) use the NLS_DATE_FORMAT to validate, display, and print dates.
In all of these cases code can provide an overriding value. For instance, you
can associate a format mask with a date field in Oracle Forms. This format mask
is used for validating input as well as displaying the date in the form.
Forms
and NLS_DATE_FORMAT
The NLS_DATE_FORMAT of
DD-MON-RR expands to DD-MON-RRRR if the date coding standards are followed.
See: APP_DATE and
FND_DATE: Date Conversion APIs, page 29-2
Date-Enhanced
Versions of Oracle Forms
Oracle Forms provides
a mechanism to differentiate the situations where the NLS_DATE_FORMAT sets
default format masks. These include:
•
BUILTIN_DATE_FORMAT
(an application property), which controls the masks used for COPY, NAME_IN, and
other built-ins. Oracle Applications sets this to "RR."
•
PLSQL
DATE_FORMAT (an application property), which controls the default mask used by
PL/SQL. Oracle Applications sets this to DD-MON-RR.
•
USER_DATE_FORMAT
(an environment variable), which controls the entry and display dates that
forms use. In Release 12, this is used to provide flexible date formats.
Length
of Dates in Oracle Forms
All date fields are of
length 11 or 20. The property class (TEXT_ITEM_DATE or TEXT_ITEM_DATETIME) sets
this automatically.
NOTE: If a field is
set incorrectly, the date may be displayed incorrectly. For example, if the
Maximum Length is 9 instead of 11, the date is automatically displayed as
"DD-MON-YY" instead of "DD-MON-YYYY." Also, if you use the
NAME_IN function on this field, the date will be returned as
"DD-MON-YY" or "DD-MON-RR" depending
on whether the
date-enhanced version of Forms is used and what the BUILTIN_DATE_FORMAT is set
to.
Display Width is the
display width in 1/100 inches. This should be 1200 (1.2 inches) for DATE fields
and 1700 (1.7 inches) for DATETIME fields.
Use
APPCORE Library APP_DATE Routines
When getting a date
out of or placing a date into a form field, use the appropriate APP_DATE
routine. You should also use the APP_DATE routine when dealing with a date in a
character field.
See: APP_DATE and
FND_DATE: Date Conversion APIs, page 29-2
Date
Format in DECODE and NVL
Always supply a date
format when using DECODE and NVL to avoid an implicit conversion. If you do not
provide a format there is a danger that the function will return a CHAR value
rather than the DATE type the code expects. The following demonstrate correct
usage with a supplied date format:
DECODE(char_col,'<NULL>',to_date(null),
to_date(char_col,'YYYY/MM/DD'))
NVL(to_date(null),to_date(char_col,'YYYY/MM/DD'))
Explicit
and Implicit Date Formats
Always specify an
explicit format when converting a date to a string; never accept the default
value of NLS_DATE_FORMAT. Some conversions are subtle; the conversion to a
string can be implicit:
select sysdate into :my_char
from dual
In the following
example the date type is converted to a character without the use of an
explicit TO_CHAR.
select to_char(sysdate,
'YYYY/MM/DD HH24:MI:SS') into :my_char
Avoid all types of
implicit conversion in code; always control the format mask. The use of an
implicit mask causes problems if the NLS_DATE_FORMAT variable is changed. The
use of implicit conversions creates unpredictable and misleading code.
Copying
Between Date Fields
You cannot directly
copy a hardcoded date value into a field:
copy('01-FEB-2001',
'bar.lamb');
The month segment, for
example "FEB", varies across the different languages, so a direct
copy is infeasible. Instead, you may call:
app_item.copy_date('01-02-2001',
'bar.lamb');
This routine does the
copy in this way:
copy(to_char(to_date('01-01-2001',
'DD-MM-YYYY'),
'DD-MON-YYYY'), 'bar.lamb');
The only format that
the NAME_IN and COPY functions accept are DD-MON-YYYY. Cast all date values to
that mask, since these functions process everything as if they are CHAR values.
SYSDATE
and USER
Instead of the Oracle
Forms built-in routines SYSDATE and USER, use the Applications functions:
FND_STANDARD.SYSTEM_DATE return DATE;
FND_STANDARD.USER return VARCHAR2;
These functions behave
identically to the built-ins, but are more efficient since they use information
already cached elsewhere.
Use these FND_STANDARD
functions in Oracle Forms PL/SQL code only; you can use the Oracle Forms
built-ins in SQL statements, $$DBDATE$$ defaulting or in stored procedures.
•
Minimize
references to SYSDATE within client-side PL/SQL. Each reference is translated
to a SQL statement and causes a round-trip to the server.
•
Time
is included in SYSDATE and FND_STANDARD.SYSTEM_ DATE by default.
Include the time for
creation dates and last updated dates. If you do not wish to include the time
in the date, you must explicitly truncate it:
:BLOCK.DATE_FIELD :=
TRUNC(FND_STANDARD.SYSTEM_DATE);
Truncate the time for
start dates and end dates that enable/disable data.
•
Use
$$DBDATE$$ to default a date value on a new record.
Troubleshooting
The section lists some
of the most common problems. Where appropriate, it also provides ways to verify
that your code avoids these year 2000 compliance problems.
Use the DATECHECK Script to Identify Issues
To identify problems,
first run the datecheck script available at Oracle's Year 2000 web site
(www.oracle.com/year2000). The output identifies both the location and the type
of problem. Consult the checklist below for instructions on each issue. Year
2000 and Related Problems:
•
DE-1.
Using a DD-MON-YY Mask With a TO_DATE, page 26-9
•
DE-2.
Using Dates Between 1999 and 2049 As Reference Dates, page 26-10
•
DE-3.
Using a DD-MON-YYYY Mask With a Two-Digit Year, page 26-10
•
DE-4.
Associating any Hardcoded Date Mask With a Form Field, page 26-11
•
DE-5.
Using a pre-1950 date With a Two-Digit Year, page 26-11
Problems with
Translated Dates:
•
TD-1.
Hardcoded English month, page 26-12
•
TD-2.
NEXT_DAY with English day or ordinal, page 26-12
Client Date Issue:
•
CD-1.
Getting the Date from the Client, page 26-12
Problems Observed During Testing
Testing is also
recommended, especially around problem dates such as December 31, 1999, January
1, 2000, January 3, 2000, February 29, 2000, December 31, 2000, and January 1,
2001.
Determining
Whether an Issue Is Year 2000 Related
Oracle's definition of
a Year 2000 bug is a bug caused by the century changeover or leap year.
Indications of Year 2000 bugs are:
•
Only
happens when system date is 2000 (or February 29, 2000)
•
Only
happens when entry date is 2000 (or February 29, 2000)
•
Get
an "ORA-1841 - (full) year must be between -4713 and +9999, and not be
0"
•
A
year 1999 date displays/saves as 0099
•
A
year 2000 date displays/saves as 1900
Date Checklist
Year
2000 Problems
The following are Year
2000 issues.
DE-1. Using a
DD-MON-YY Mask with a TO_DATE
The correct syntax for
TO_DATE is:
my_char_date varchar2(9);
...
TO_DATE(my_char_date,'DD-MON-RR')
Do NOT use:
TO_DATE(my_char_date,'DD-MON-YY') [WRONG]
TO_DATE(my_char_date) [WRONG
- NO FORMAT MASK]
Using a DD-MON-YY mask with an Oracle Reports
Parameter: Masks of
DD-MON-YY in your
reports convert the incoming string parameters incorrectly. Masks of DD-MON-RR
or DD-MON-RRRR ensure they behave correctly for Year 2000 purposes. For
example:
MYREPORT.rex: INPUT_MASK =
<<"DD-MON-RR">> MYREPORT.rex: INPUT_MASK =
<<"DD-MON-RRRR">>
Leap year problem: Using the TO_DATE with a YY causes a
particular problem on leap year. This example illustrates why we recommend
converting all character date values to canonical format; sometimes the year
2000 problems are subtle.
my_char_date =
to_char(to_date(my_char_date,'DD-MON-YY'), 'DD-MON-YY')
Although the redundant
syntax above is confusing, as long as the character date is in the DD-MON-YY
format, it seems as if the code would work since the incorrect century is
immediately truncated.
However, if the date
is 29-FEB-00 this code fails. The year 2000 is a leap year but the year 1900
was not. The TO_DATE used with DD-MON-YY interprets the 00 as 1900, which
creates an error.
DE-2. Using Dates
Between 1999 and 2049 as Reference Dates
If you
are checking against a hardcoded reference date, do not use dates between 1999
and 2049. For example, the following code, which uses an incorrect date as a
positive infinity, will fail on December 31, 1999: my_date date; your_date date;
...
NVL(my_date,to_date('12/31/1999',DD/MM/YYYY)) = NVL(your_date,
to_date('12/31/1999',DD/MM/YYYY)
[WRONG]
Instead, use dates
that are truly impossible to reach:
NVL(my_date, to_date('01/01/1000',DD/MM/YYYY)) =
NVL(your_date, to_date('01/01/1000',DD/MM/YYYY)
DE-3. Using a
DD-MON-YYYY Mask with a Two-Digit Year
If a date stored as a
nine character string is converted to a date using an eleven-digit mask such as
DD-MON-YYYY, the date is moved to the first century. For example:
my_rr_date varchar2(9);
my_date date; my_date2 date;
... my_date2 := to_date(my_rr_date,'DD-MON-YYYY') [WRONG]
The date stored in
my_rr_date variable is now stored as a first century date in my_date2. If
my_rr_date was 30-OCT-99, my_date2 is now 30-OCT-0099.
If my_rr_date was in
the year 2000, the code moves the date to the year 0, which did not exist. The
Oracle Error ORA-01841 warns of this kind of error.
To avoid these
problems, avoid unnecessary TO_DATE conversions or use the DD-MON-RR mask to
convert the date (if a TO_DATE is required):
Oracle Applications Developer's Guide
my_date2 := my_date my_date2 := to_date(my_rr_date,'DD-MON-RR')
Implicit Conversions: Accidental conversions of this type may
occur by performing a TO_DATE on a date type value. This only occurs in SQL or
server side PL/SQL. In SQL, performing a TO_DATE on a date type implicitly does
a TO_CHAR on that value since TO_DATE requires a character argument. The
TO_CHAR is done using a nine-digit format mask (DD-MON-YY), which causes the
problems discussed above. This problem occurs in server-side PL/SQL such as C
programs, SQL*Forms 2.3 code, and dynamic SQL in Developer 2000.
select to_date(my_date,'DD-MON-YYYY')... [WRONG]
Instead, avoid the
unnecessary conversion:
select my_date...
Similar accidental
conversions can be done by using NVL and DECODE carelessly. If a NVL or DECODE
is returning a character instead of a date, trying to correct this error by
converting the returned value to a date can cause the first century error:
to_date(DECODE(char_col,'<NULL>',null,sysdate),
'DD-MON-YYYY') [WRONG]
to_date(NVL(null,sysdate),'DD-MON-YYYY') [WRONG]
Instead, ensure that
the returned value is a date type:
DECODE(char_col,'<NULL>',to_date(null),sysdate)
NVL( to_date(null),sysdate)
ORA-1841 Problems: In the year 2000, transferring dates to the
first century causes an immediate problem. For dates occurring in the year
2000, there is no first century equivalent (there is no year 0). If your code
converts a date to year 0, the error "ORA-01841: (full) year must be
between -4713 and +9999, and not be 0" occurs.
Comparison Problems: Also, when comparing date values,
converting the dates to the first century causes problems with comparisons
across the century boundary. Although 01-JAN-99 occurs before 01-JAN-01 in the
DD-MON-RR format, 01-JAN-0099 is later than 01-JAN-0001 if the dates are
accidentally moved to the first century.
DE-4. Associating Any
Hardcoded Date Mask with a Form Field
Any Oracle Forms field
with a hardcoded mask associated with it behaves incorrectly since the standard
date fields use the mask DD-MON-RRRR.
In Release 12,
flexible date formats allow the format to change depending on the environment.
DE-5. Using a Pre-1950
Date with a Two-Digit Year
Oracle Applications
uses DD-MON-RR mask as the default date mask. If century information is
missing, the default code "assumes" a date is between 1950 and 2049.
Hardcoded dates before
1950 stored with a two-digit year will be misinterpreted. A hardcoded date with
a four-digit year (using an explicit format mask) between 1900 and 1949 is only
incorrect if the date is stored without century information (usually meaning
Handling Dates
it is stored as a
DD-MON-RR string). Most problems of this time are in C code or concurrent
program arguments although they are possible in PL/SQL.
Use the standard
negative and positive infinity dates in all new code. Of course, in SQL and
PL/SQL you still need to ensure that the century information is not lost.
For example, the code
fragment to_date('01-JAN-00') would be interpreted as January 1, 2000, while
the code fragment to_date('01/01/1000', 'DD/MM/YYYY) would be unambiguous.
Translated
Date Issues
These issues will
affect any dates that must work in a multilingual environment. Oracle
Applications Release 12 can run in multiple languages and can support multiple
date formats.
TD-1. Hardcoded
English Month
English months fail in
other languages. Use a numeric month instead.
TO_DATE('1000/01/01','YYYY/MM/DD')
Not:
TO_DATE('01-JAN-1000','DD-MON-YYYY') [WRONG]
TD-2. NEXT_DAY with
English Day or Ordinal
A next_day call is not
translatable if you pass in a hardcoded English day (i.e. MON). However, it is
also incorrect to pass it a hardcoded ordinal (i.e. 1), since which days map to
which numbers varies by territory.
Use a currently known
date (i.e. 11/3/1997 is a Monday) to determine what the 3 character day in the
current language is and then pass that in.
next_day(my_date,to_char(to_date('1997/03/11',
'YYYY/MM/DD'),'DY'))
Client
Date Issues
The following is a
client date issue.
Client Date Issues -
CD-1. Getting the Date from the Client
These problems are
caused by the program getting the current day or time from the client machine
(a PC in the smart client release) instead of the database. The database is
preferable. Oracle Applications currently gets all current times from the
server because neither PC vendors nor Microsoft are providing Year 2000
warranties.
Do not use $$DATE$$ to
default the current date into a Forms field. This gets the client date. Instead
use the $$DBDATE$$ built-in which gets the database date. Better still, default
the date programmatically in WHEN-CREATE-RECORD or WHEN-NEW- FORM-INSTANCE
using FND_STANDARD.SYSTEM_DATE. The use of $$DATE$$ is not a problem in
character mode (it uses code similar to the SYSTEM_DATE call).
