This content has been marked as final. Show 23 replies
try to use something like this.
:H_TRANS_DATE := TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
Set H_TRANS_DATE 's datatype as DateTime
re-write the When-validate-item as
:H_TRANS_DATE := sysdate;
This will not cause any invalid month error even if your format mask changes..
Edited by: Dora on Nov 18, 2009 1:29 PM
u may try this too.
:H_TRANS_DATE := TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS');
Actually what I want the system date and time should be initializes automatically at the insert mode and the user can renter the date but time should be initialize from the oracle server, and what I notice the second is changing but the minute is not changing always the same for example 18/11/2009 03:11:59 it became as in second insert as 18/11/2009 03:11:35 that the minute is not changing ,and what is the different between $$dbdatetime$$ and $$datetime$$ in the initial value of the property
Use $$DATE$$, $$DATETIME$$, and $$TIME$$ to obtain the local system date/time; use $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ to obtain the database date/time, which may differ from the local system date/time if, for example, you're connecting to a remote database in a different time zone.
[Date and Time|http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch4datetime.htm#i1006333]
Thank you very much for your cooperation and help it was very helpful
And I want in the second field, if the user only enters the date without the time then it will take it from this script
IF :H_EXIT_DATE IS NOT NULL THEN
LOC_VAR := TO_CHAR(:H_EXIT_DATE,'DD/MM/YYYY')||''||TO_CHAR(SYSDATE,'HH12:MI:SS');
And the above script is write in WHEN-VALIDATE-ITEM trigger
But I am getting the following message when I press enter key
FRM-40735 WHEN-VALIDATE-ITEM TRIGGER RAISED UNHANDLED EXCEPTION ORA-01843
And also I want some time if the user enter the date and time, how can I check if the user have been enter the time with the date, so that I do not want the above script to bee executed if he enter the time with date,and I want it to be executed when he enter only the date without the time.
is the data type is Date or char please check.
Yes the data type is Date, but I want the data type as datetime,but when I make it as datetime I am getting this message
FRM-50025 Date/time must be entered in a format like DD/MM/YYYY HH24:MI[:SS]
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_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.)
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:
DATETIME: FXFM<YYYY_mask> HH24:MI:SS
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:
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:
DATETIME: <YYYY_mask> HH24:MI[:SS]
In the registry :
If its Correct/Helpful please Mark it thanks.
Edited by: S@R@h on Nov 19, 2009 12:34 AM
I already set NLS_DATE_FORMAT=DD-MON-YYYY
In my registry
But I am getting same message
do u have any attach library?