Y2K in D2K ?

I have personal oracle 7.3 on windows, and d2k (forms 4.5, reports 2.5).
I have set the NLS_DATE_FORMAT on oracle as DD-MON-YYYY. But I observed that this does not affect Forms & Reports, where the date format is still DD-MON-YY.
could it be set right ?
LVL 1
Vin32Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
tbcoxConnect With a Mentor Commented:
You need to set the Forms default date mask, not just the Database default date mask.  

From the online help, under "About format masks for dates":

Types of date format masks

Form Builder uses the following date format masks:

1      User-created date format masks for individual items

2      PLSQL date format mask

3      Builtin date format mask

4      Database date format mask

5      Input date format mask

6      Output date format mask

7      Error date format mask

1.  Individual item's date format mask

You create and set these masks through the Format_Mask property.  As noted above, avoid creating these individual masks if the general-purpose masks will suffice.

2.  PLSQL date format mask

You set this mask in the PLSQL_Date_Format property.  It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information.  It is also recommended that you use the same setting for the Builtin date format mask.  (See the example below.)

3.  Builtin date format mask

You set this mask in the Builtin_Date_Format property .  It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information.  It is also recommended that you use the same setting for the PLSQL date format mask.  (See the example below.)

4.  Database date format mask

Each database session within a Forms application has a single Database date format mask.  This mask is used by the database server to convert a string to a date value, or vice versa, in the course of evaluating a query.

A default value for this mask is established by the Oracle Server's NLS_DATE_FORMAT (or NLS_LANG or NLS_TERRITORY) initialization parameter.  This can be overridden in all new database sessions for a particular client, by setting the client's NLS_LANG and NLS_DATE_FORMAT environment variables.  

Within a Forms application, the mask value can be further overridden on a session-by-session basis, by issuing an ALTER SESSION.  It may simplify an application's logic if it is set to the same format mask as the application's PLSQL_Date_Format and Builtin_Date_Format properties.  (See the following example.)

Note that if you do an OPEN_FORM with the SESSION option specified, then the opened form will have a new database session; therefore, you might want to alter its session, as well.  

Example for PLSQL, Builtin, and Database mask creation

To create and coordinate these masks, the PRE-FORM trigger in the application's initial form could contain:

   SET_APPLICATION_PROPERTY(PLSQL_DATE_FORMAT,     'YYYY/MM/DD HH24:MI:SS');

   SET_APPLICATION_PROPERTY(BUILTIN_DATE_FORMAT,   'YYYY/MM/DD HH24:MI:SS');

   FORMS_DDL('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY/MM/DD HH24:MI:SS''');

5.  Input date format mask

The Input date format mask (actually a set of masks) is used to extract a value from an item which has no format mask.  That is, the mask is used to convert a user-entered string into a native-format date value.

The system administrator or end user may set the environment variables FORMS60_USER_DATE_FORMAT and FORMS60_USER_DATETIME_FORMAT to specify the format masks used to extract values from items of type DATE and DATETIME, respectively.  In each case, multiple format masks may be specified, separated by vertical bars. When converting a user-entered string, the format masks are used in the order specified, until a conversion succeeds or until the format masks are exhausted.

For example, these runtime environment variables might be set as follows:

   FORMS60_USER_DATE_FORMAT       FXFMDD-MM-RRRR

   FORMS60_USER_DATETIME_FORMAT   FXFMDD-MM-RRRR HH24:MI:SS|FXFMDD-MM-RRRR HH24:MI

This would force the end user to enter values into DATE items (with no format mask) in the format exemplified by 31-6-97.  (The RRRR enables years between 1950 and 2049 to be entered with the century omitted).  But for DATETIME items, the end user could enter values either in the format exemplified by 31-6-97 13:45:30 or else in the format exemplified by 31-6-97 13:45 (which would be interpreted as 31-6-97 13:45:00).

If either of these two runtime environment variables is not defined, then Form Builder will construct default format masks, as explained below.

6.  Output date format mask

The Output date format mask is used to display a value in an item which has no format mask, or in an LOV column.  There are actually two such masks, one for DATE items and LOV columns, and one for DATETIME items. (DATETIME LOV columns do not exist.).  The system administrator or end user may set the environment variables FORMS60_OUTPUT_DATE_FORMAT and FORMS60_OUTPUT_DATETIME_FORMAT to specify these format masks explicitly.

If FORMS60_OUTPUT_DATE_FORMAT is not defined, but FORMS60_USER_DATE_FORMAT is defined, then the Output date format mask for DATE items is derived from FORMS60_USER_DATE_FORMAT by taking the first format mask (the value up to the first vertical bar) and stripping out all occurrences of FX and FM.

For example, if FORMS60_OUTPUT_DATE_FORMAT is not defined and FORMS60_USER_DATE_FORMAT is defined to be FXFMDD-MM-RRRR|FXFM-MON-RRRR, then the Output date format mask for DATE items is DD-MM-RRRR.

If neither FORMS60_OUTPUT_DATE_FORMAT nor FORMS60_USER_DATE_FORMAT is defined, then Form Builder will construct default format masks, as explained below.

There are similar rules for DATETIME items.

7.  Error date format mask

There is also an Error format mask, which Form Builder uses in producing certain error messages that are issued when an end user enters an invalid value into a date item.  (There are actually two such masks -- one for DATE items and one for DATETIME items.)

For example:

   FRM-50012: Date must be entered in a format like <error_date_format>

   FRM-50025: Date/time must be entered in a format like <error_datetime_format>

The system administrator or end user may set the environment variables FORMS60_ERROR_DATE_FORMAT and FORMS60_ERROR_DATETIME_FORMAT to specify these format masks explicitly.

If FORMS60_ERROR_DATE_FORMAT is not defined, but FORMS60_USER_DATE_FORMAT is defined, then the Error date format mask for DATE items is simply taken to be the entire value FORMS60_USER_DATE_FORMAT, including vertical bars.

If neither FORMS60_ERROR_DATE_FORMAT nor FORMS60_USER_DATE_FORMAT is defined, then Form Builder will construct default format masks, as explained below.

There are similar rules for DATETIME items.

Default values for the Input, Output, and Error masks

If the runtime environment variables FORMS60_USER_DATE_FORMAT and FORMS60_USER_DATETIME_FORMAT are not both set, then the rules above do not specify all of the Input, Output, and Error format masks for DATE items and DATETIME items.  It is recommended that these environment variables both be set.  That will avoid having the complex rules below come into play.

In general, Form Builder assigns default values that provide compatibility with Release 4.5 and earlier.  The behavior in those releases may actually differ slightly in some cases, but in such cases the behavior in prior releases is considered to be erroneous.

The following rules distinguish between DATE2 and DATE4 items (see Builtin_Date_Format property for details), with the additional proviso that an LOV column whose datatype is DATE is considered to be a DATE2 object.  Note that there are no DATETIME LOV columns, and LOV columns do not directly participate in builtins.

Form Builder starts with the format mask derived from the current NLS environment. This may be specified explicitly by setting the environment variable NLS_DATE_FORMAT.  Otherwise, NLS will compute a default format mask based on the current language.  Call this NLS format mask "<YY_mask>".

Next, Form Builder computes a second format mask (call it "<YYYY_mask>") from <YY_mask> as follows:  If <YY_mask> contains "YY" but not "YYYY", then <YYYY_mask> is set to <YY_mask>, but with the "YY" replaced by "YYYY". Similarly, "yy", "RR", or "rr" will be replaced by "yyyy", "RRRR", or "rrrr" in <YYYY_mask>.  Otherwise, <YYYY_mask> is set to <YY_mask>.

Given the above, the remaining unspecified Input format masks for DATE2, DATE4, and DATETIME objects become:

   DATE2:    FXFM<YY_mask>

   DATE4:    FXFM<YYYY_mask>

   DATETIME: FXFM<YYYY_mask> HH24:MI:SS

             FXFM<YYYY_mask> HH24:MI

The leading FXFM is omitted if the format mask already contains an FX or FM.

Next, any occurrences of FX and FM are removed from <YY_mask> and <YYYY_mask>.

Given the above, the remaining unspecified Output format masks become:

   DATE2:    <YY_mask>

   DATE4:    <YYYY_mask>

   DATETIME: <YYYY_mask> HH24:MI:SS

Finally, for the remaining unspecified Error format masks, the first occurrence (if any) of RR, rr, RRRR, or rrrr in <YY_mask> and <YYYY_mask> is replaced by the corresponding number of Ys or ys.

Given the above, the remaining unspecified Error format masks become:

   DATE2:    <YY_mask>

   DATE4:    <YYYY_mask>

   DATETIME: <YYYY_mask> HH24:MI[:SS]
0
 
avicoCommented:
In the registry :

HKEY_LOCAL_MACHINE\Software\Oracle

NLS_DATE_FORMAT=DD-MON-YYYY
0
 
Vin32Author Commented:
good suggestion, avico, but i've already done the registry entry. in fact this is the only way you can change the default date format in personal oracle: an entry in init.ora file has no effect.
i am rejecting your answer only because i need another solution.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
manand062397Commented:
You should use DD-MON-RRRR instead of DD-MON-YYYY.
0
 
Vin32Author Commented:
RRRR and YYYY make only one difference -- the range for "current century" is defined differently for these formats.
my problem is that even after i've configured for YYYY (or RRRR), Forms and Reports still do not reflect the new date format. Their default is still YY !!
0
 
avicoCommented:
Try setting the USER_NLS_DATE_FORMAT registry key, instead of the NLS_DATE_FORMAT. To my knowledge, there's a key called USER_NLS_LANG which differs from NLS_LANG by the way that it applies to the client software (e.g. d2k). Perhaps it also applies for NLS_DATE_FORMAT.

0
 
MANIKANDANCommented:
TRY QUERYING FROM UR SQL,SINCE UR REGEDIT IS SET AS
DD-MON-YYYY IT SHOULD RETURN THE DATE FIELD IN THIS FORMAT ONLY.
             IF STILL PROBLEM EXISTS THEN
TRY ,
   "ALTER SESSION SET_NLS_DATE_FORMAT = "DD-MON-YYYY"
  AND CHECK THE DATA .
  IF STILL PROBLEM ,
         CHECK THE DATABASE DATE FORMAT .
0
 
Vin32Author Commented:
Hi all,

i was away for the weekend and only now i was able to try out the suggestions.

the answer by tbcox and the suggestion by avico both work fine. thanx a lot. now i'd like to give points to both of you. how to do that ?

Manikandan, your method is also fine, but i forgot to mention in my question that i had tried this out before even posting the question. my mistake. but then i'd have to do this "alter session" in each application that i build. which i would like to avoid. thanx anyway.
0
 
Vin32Author Commented:
in fact, i am accepting the answer by tbcox. i'd also like to know, tbcox, whether similar parameters are there for reports and menus and any other oracle product.

and avico, i'd post a dummy question for you, and you just have to post an answer.
0
All Courses

From novice to tech pro — start learning today.