This chapter provides you with information you need to handle dates correctly in your code.
Overview of Dates in Oracle E-Business Suite
Date Coding Standards
Troubleshooting
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, concurrent programs, Oracle Reports, Java code, flexfield columns, form fields, and profile values.
Before continuing the discussion of how dates are used in Oracle E-Business Suite, it is helpful to establish some definitions.
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 E-Business Suite 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.
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, 2017 can be represented as 11-MAR-2017, 03/11/2017, or as 2017/03/11.
A default format mask variable (NLS_DATE_FORMAT) determines the format mask unless a different mask is explicitly set. Oracle E-Business Suite sets the NLS_DATE_FORMAT to be DD-MON-RR.
When dates are stored in a character format, one standard format, called the canonical date format, is used to prevent confusion and inconsistencies.
Oracle E-Business Suite 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 (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.
Oracle E-Business Suite does not support BC dates, so dates before Julian 1,721,424 are not used.
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:
| Variable | Description |
|---|---|
| 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/2017, 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.
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 E-Business Suite standards require an explicit format mask.
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 are encoded using Julian dates.
Oracle E-Business Suite 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 E-Business Suite 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.
Oracle E-Business Suite introduced year 2000 compliance in Release 10.7. Releases 11, 11i, and 12 continue year 2000 support.
Year 2000 compliance ensures that there is never any confusion as to which century the date refers. Date values in Oracle E-Business Suite appear in form screens and form code, are used in concurrent programs, and are manipulated and stored in the database. Custom extensions and modifications to Oracle E-Business Suite also use date values in custom forms, tables, APIs, concurrent programs, and other code. This section discusses the steps that ensure that dates used in custom extensions and modifications to Oracle E-Business Suite meet the requirements for year 2000 compliance.
Oracle uses a definition of year 2000 compliance based on a superset of the British Standards Institute's definition. This definition spells out five factors in satisfying year 2000 compliance for date processing:
The application must correctly handle date information before, during, and after January 1st, 2000. The application must accept date input, provide date output, and perform calculations on dates throughout this range.
The application must function according to documentation without changes in operation resulting from the advent of the new century.
Where appropriate, the application must respond to two-digit input in a way that resolves the ambiguity as to century in a defined and predetermined manner.
The application must store and provide output of date information in unambiguous ways.
The application must correctly manage the leap year occurring in the year 2000. February 29, 2000 is of particular concern because there was no February 29, 1900.
By following the standards outlined in this section, your code will avoid the major Y2K issues found in the Oracle E-Business Suite environment.
There are several principles of coding with dates that are applied wherever dates are used by Oracle E-Business Suite. 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')
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.
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
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 E-Business Suite sets this to "RR."
PLSQL DATE_FORMAT (an application property), which controls the default mask used by PL/SQL. Oracle E-Business Suite 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.
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.
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
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'))
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.
You cannot directly copy a hardcoded date value into a field:
copy('01-FEB-2017', '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-2017', 'bar.lamb');
This routine does the copy in this way:
copy(to_char(to_date('01-01-2017', '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.
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.
The section lists some of the most common problems. Where appropriate, it also provides ways to verify that your code avoids these.
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.
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
The following are Year 2000 issues.
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.
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)
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):
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.
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.
Oracle E-Business Suite 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 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.
These issues will affect any dates that must work in a multilingual environment. Oracle E-Business Suite Release 12 can run in multiple languages and can support multiple date formats.
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]
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/2017 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'))
The following is a client date issue.
These problems are caused by the program getting the current day or time from the client machine instead of the database. The database is preferable. Oracle E-Business Suite currently gets all current times from the server.
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.