How to work and trouble shoot with Dates in Oracle Apps

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).