| How-To Document
Work with Dates
After completing this How-To, you should be able to:
Table of Contents
When using Oracle HTML DB to build applications, it is important to understand how to process dates rendered in different formats. This document describes how to process dates when building applications in Oracle HTML DB.
To demonstrate processing dates in Oracle HTML DB, you first need to create a report and form on the EMP table.
To create a report and form on the EMP table:
If you run the report page you just created, it should look similar to the following figure.
Figure 1: Default Date Format in Report
Notice that the date is in the default format for this database, DD-MON-YY. If you would like to display dates in the MM/DD/YYYY format, you can add a format mask to this column in the report.
To add a format mask to the Hiredate column:
Now when you run the report page, the Hiredate column should look similar to the following figure.
Figure 2: Applying a Format Mask to a ColumnChanging the Date Format in a Form (Using a Date Picker)
Now that the report is showing dates properly, you can change the format of the form item for Hiredate on the form page, by changing the Date Picker format to match the format used for the Hiredate column on the report page. To do this, you change the Display As of the form item for Hiredate.
To change the Hiredate form item to use Date Picker (DD/MM/YYYY):
Run the report page and click on an edit icon. Notice the error you receive, similar to figure 3.
Figure 3: Date Picker Error
The exception is raised because the date picker format is MM/DD/YYYY, yet the value retrieved from the database is in the default date format for this database, DD-MON-YY. Now, click Cancel and then Create on the report page to create a new entry. Enter information for a new employee and click Create. You should receive an error similar to figure 4.
Figure 4: Invalid Month Error
To correct these errors, edit the attributes of the Hiredate item and scroll down to the Source attributes. As shown in figure 5, the Source Type of the item is set to Database Column.
Figure 5: Hiredate Item Source
Figure 5 shows that the item called P6_HIREDATE is bound to the database column HIREDATE. The first error occurred because HIREDATE was retrieved from the database using the database default DD-MON-YY, but the item specified that it should use a date picker with the format MM/DD/YYYY.
The second error occurred because the process created by the wizard to insert data into the EMP table was expecting HIREDATE in the default format for this database, DD-MON-YY, but it was passed to the process in the format MM/DD/YYYY.
To fix both issues, you apply a format mask to the item. Enter MM/DD/YYYY in the Format Mask field as shown in Figure 6. The format mask should match the format of the Date Picker chosen for the item.
Figure 6: Add a Format Mask
When you run the application after adding a format mask you will be able to edit and create entries without error.Handling Dates in a Custom Process
If you wrote your own process to insert rows into the EMP table instead of using a process created by the Report with Links to Form on a Table wizard, you would need to handle the date format for the value to be inserted into the HIREDATE column. For example, suppose your process was similar to the following.
Since the Date Picker used for the Hiredate item is in the format MM/DD/YYYY, you will need to apply the TO_DATE function to the value for the HIREDATE column, specifying the format of the date is MM/DD/YYYY as shown in the following.
BEGINUsing an Application Level Format Mask to Customize a Date Picker
If you need to represent dates in another format than those provided by the Date Picker item types, you have the option of using a custom date format with a Date Picker.
To use a custom date picker in an application:
The following procedures explains how to to change the date format in your application to YYYY-MM-DD.
To create a Substitution String called PICK_DATE_FORMAT_MASK with a value of YYYY-MM-DD:
Figure 7: Create PICK_DATE_FORMAT_MASK
To change the Hiredate item Display As and Format Mask:
To change the format mask for the HIREDATE column on the report page:
After making the changes above, the report page and the form page should look similar to figures 8 and 9.
Figure 8: Hiredate Column YYYY-MM-DD
Figure 9: Hiredate Form Item YYYY-MM-DDValidating Date Formats in a Form
Using a Date Picker item type in a form does not guarantee that the date format will be entered correctly. The user can choose not to use the picker and type directly in that field. If they enter an invalid month, 13 for example, they will receive an error from the database like the one seen above in figure 4. Specifically, they will receive "ORA-01843: Not a valid month" as the error message.
A better way to handle invalid user input is to validate it before it is passed to the process. If the format is invalid, the user is notified and returned to the form with a custom error message in-line.
To validate the user input for the Hiredate field, you add a validation to the form page. If you are running Oracle HTML DB in an Oracle 10 g database, you can use a Regular Expression to validate the user input. If the database is not 10 g you need to create a validation of type Function Returning Boolean. With the Function Returning Boolean method, you write a PL/SQL function to evaluate the input and return true or false depending on whether the date is in the proper format.The following procedures describe how to create a Regular Expression validation and a Function Returning Boolean validation to validate that the value entered in the Hiredate field is in the format YYYY-MM-DD.
To create a Regular Expression validation on the Hiredate field: (10 g only)
Figure 10: Adding a Regular Expression Validation on Hiredate
To create a Function Returning Boolean validation on the Hiredate field:
Figure 11: Adding a Function Returning Boolean Validation on Hiredate
If the user enters an invalid date format now, they are returned to the page with an in-line error message, similar to figure 12.
Figure 12: In-Line Validation Error for HiredateAdditional Resources
To learn more about working with dates, see "Datetime Format Elements" in the SQL Reference and "Date and Time Parameters" in the Globalization Support Guide .
Feel free to discuss this how-to in the OTN HTML DB Discussion Forum.