14.3.1 FM: Suppressing Blanks and Zeros

PL/SQL offers the FM element as a modifier to a format mask. FM (fill mode) controls the suppression of padded blanks and leading zeros in values returned by the TO_CHAR function.

By default, the following format mask results in both padded blanks and leading zeros (there are five spaces between the month name and the day number):

TO_CHAR (SYSDATE, 'Month DD, YYYY') ==> 'April     05, 1994'

With the FM modifier at the beginning of the format mask, however, both the extra blank and the leading zeros disappear:

TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') ==> April 5, 1994'

The modifier can be specified in upper-, lower-, or mixed-case; the effect is the same.

The FM modifier is a toggle, and can appear more than once in a format model. Each time it appears in the format, it changes the effect of the modifier. By default (that is, if FM is not specified anywhere in a format mask), blanks are not suppressed and leading zeros are included in the result value. So the first time that FM appears in the format it indicates that blanks and leading zeros are suppressed for any following elements. The second time that FM appears in the format, it indicates that blanks and leading zeros are not suppressed for any following elements, and so on.

In the following example I suppress the padded blank at the end of the month name, but preserve the leading zero on the day number with a second specification of FM:

TO_CHAR (SYSDATE, 'fmMonth FMDD, YYYY') ==> April 05, 1994'

If you do not use FM in your mask, a converted date value is always right-padded with blanks to a fixed length (that length is dependent on the different format elements you use). When you do use FM, on the other hand, the length of your return value may vary depending on the actual values returned by the different format elements.

When you do not use FM to convert a number to a character string, the resulting value is always left-padded with blanks so that the number is right-justified to the length specified by the format (or declaration of the variable). When you do use FM, the left-padded blanks are suppressed and the resulting value is left-justified.

Here are some examples of the impact of FM on numbers converted with TO_CHAR:

TO_CHAR (8889.77, 'L9999D99')   ==> '      $8889.77'
TO_CHAR (8889.77, 'fmL9999D99') ==> '$8889.77'

The FM modifier can also be used in the format model of a call to the TO_DATE function to fill a string with blanks or zeros to match the format model. This variation of FM is explored in the discussion of FX.

14.3.2 FX: Matching Formats Exactly

PL/SQL offers the FX element as a modifier to a format mask. FX (format exact) specifies that an exact match must be performed for a character argument and date format mask in a call to the TO_DATE function.

If FX is not specified, the TO_DATE function does not require that the character string match the format precisely. It makes the following allowances:

• Extra blanks in the character string are ignored. Blanks are not significant data in any of the parts of a date value, except to delimit separate parts of the date and time:

  TO_DATE ('Jan         15       1994', 'MON DD YYYY') ==> 15-JAN-1994

• Numeric values, such as the day number or the year, do not have to include leading zeros to fill out the mask. As long as the numbers are in the right place in the string (as determined, usually, by the delimiter characters in the string), TO_DATE can convert the numeric values properly:

  TO_DATE ('1-1-4', 'DD-MM-YYYY') ==> 01-JAN-0004
  TO_DATE ('7/16/94', 'MM/DD/YY') ==> 14-JUL-1994

• Punctuation and literals in the string to be converted can simply match the length and position of punctuation and quoted text in the format. In the following example, my format mask specifies hyphen ( - ) delimiters and the text "WhatIsaynotdo" between the day and the year. The string that I pass it, on the other hand, uses caret ( ^ ) delimiters and "the year of" for the embedded text. Because both of the two literals inside quotes have the same number of characters, TO_DATE has no problem making the match.

TO_DATE ('JANUARY^1^ the year of 94', 'Month-dd-"WhatIsaynotdo"yy')
==> 01-JAN-1994

This kind of flexibility is great -- until you want to actually restrict a user or even a batch process from entering data in a nonstandard format. In some cases, it simply is not a reflection of everything being OK when a date string has a pound sign ( # ) instead of a hyphen ( - ) between the day and month numbers. For these situations, you can use the FX modifier to enforce an exact match between string and format model.

With FX, there is no flexibility for interpretation of the string. It cannot have extra blanks if none are found in the model. Its numeric values must include leading zeros if the format model specifies additional digits. And the punctuation and literals must exactly match the punctuation and quoted text of the format mask (except for case, which is always ignored). In all of the following examples, PL/SQL raises one of the following errors:

ORA-01861: literal does not match format string
ORA-01862: wrong number of digits for this format item

TO_DATE ('Jan 15 1994', 'fxMON DD YYYY')
TO_DATE ('1-1-4', 'fxDD-MM-YYYY')
TO_DATE ('7/16/94', 'FXMM/DD/YY')
TO_DATE ('JANUARY^1^ the year of 94', 'FXMonth-dd-"WhatIsaynotdo"yy')

The FX modifier can be specified in upper-, lower-, or mixed-case; the effect is the same.

The FX modifier is a toggle, and can appear more than once in a format model. Each time it appears in the format, it changes the effect of the modifier. By default (that is, if FX is not specified anywhere in a format mask), an exact match is not required in any part of the string (as described above). So the first time that FX appears in the format it turns on exact matching for any following elements. The second time that FX appears in the format it indicates that an exact match is not required for any following elements, and so on.

In the following example I specify FX three times. As a result, an exact match is required for the day number and the year number, but not the month number:

TO_DATE ('07-1-1994', 'FXDD-FXMM-FXYYYY') ==> 07-JUL-1994

This next attempt at date conversion will raise ORA-01862 because the year number is not fully specified:

TO_DATE ('07-1-94', 'FXDD-FXMM-FXYYYY') -- Invalid string for format!

You saw in the previous section how the FM modifier would strip leading blanks and zeros from the output of a call to TO_CHAR. You can also use FM in the format model of a call to the TO_DATE function to fill a string with blanks or zeros. This action matches the format model (the opposite of the suppression action). You can, in other words, use FM to guarantee that a format exact match required by FX will succeed. The following call to TO_DATE will return a date because the fm at the beginning of the format mask turns on fill mode for the entire string, thus changing the 1 to 01 and 94 to 1994:

TO_DATE ('07-1-94', 'FXfmDD-FXMM-FXYYYY')

You can also include multiple references to both FM and FX in the same format string, to toggle both or either of these modifiers.

14.3.3 RR: Changing Millenia

We are coming up fast on the end of the 20th century. How many of your programs will still work when the clock ticks over midnight on December 31, 1999? Many of your Oracle-based applications should be well protected since you have been able to take advantage of a true date datatype. In other words, you haven't had to write any special programs to manually manipulate dates, thereby leaving yourself vulnerable. On the other hand, most everyone has been using a two-digit year in their date format masks, either inherited from the default DD-MON-YY or with common substitutes like MM/DD/YY.

The two-digit year format elements could give you problems when the century and millenium are close to changing. The YY format element always defaults to the current century. So when it is November 1999 and your user enters 1/1/1 or 1-JAN-1, they will enter into the database the date of January 1, 1901 -- not January 1, 2001, as they might have been thinking.

What's an IS manager to do? One solution is to go into all your screens and change or add trigger logic so that if the user enters a year number less than ten (or whatever you decide the cutoff to be), then the next century will be assumed. That will work, but it surely must be a most undesirable prospect.

Fortunately, Oracle7 provides a new format element to take care of this problem: the RR format model. With RR you can enter dates from the 21st century before the year 2000 and you can enter dates from the 20th century after the year 2000 (like the birthdays of employees and customers). Here is how RR works:

If the current year is in the first half of the century (years through 49) then:

• If you enter a date in the first half of the century, RR returns the current century.

• On the other hand, if you enter a date in the latter half of the century, RR returns the previous century.

• If the current year is in the latter half of the century (years 50 through 99) then:

  • If you enter a date in the first half of the century, RR returns the next century.

  • If you enter a date in the latter half of the century, RR returns the current century.

Here are some examples of the impact of RR. Notice that the same year numbers are returned for Year 88 and Year 18, even though SYSDATE returns a current date in the 20th and 21st centuries, respectively:

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date",
       TO_CHAR (TO_DATE ('14-OCT-88', 'DD-MON-RR'), 'YYYY') "Year 88",
       TO_CHAR (TO_DATE ('14-OCT-18', 'DD-MON-RR'), 'YYYY') "Year 18"
  FROM dual;

Current Date  Year 88  Year 18
------------  -------  -------
  11/14/1994     1988     2018


SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') "Current Date",
       TO_CHAR (TO_DATE ('10/14/88', 'MM/DD/RR'), 'YYYY') "Year 88",
       TO_CHAR (TO_DATE ('10/14/18', 'MM/DD/RR'), 'YYYY') "Year 18"
  FROM dual;

Current Date  Year 88  Year 18
------------  -------  -------
  11/14/2001     1988     2018

Of course, if you use the RR format after the year 2000 and want to enter a date that falls in the latter half of the 21st century, you will need to add special logic. Masks with the RR format model will always convert such two-digit years into the previous century.

There are a number of ways you can activate the RR logic in your current applications. The cleanest and simplest way is to change the default format mask for dates in your database instance(s). You can do this by changing the NLS_DATE_FORMAT initialization parameter as follows:

NLS_DATE_FORMAT = 'MM/DD/RR'

or:

NLS_DATE_FORMAT = 'DD-MON-RR'

depending on what the previous format was. Then, if you have not hardcoded the date format mask anywhere else in your screens or reports, you are done. Bring down and restart the database and then your application will allow users to enter dates in the 21st century. If you do have date format masks in the format property for an Oracle Forms item or in an Oracle Reports query or field, you will need to change those modules to reflect the new approach embodied by RR.

14.3.4 Using TO_CHAR to Create a Date Range

At times, users want information about activity on a specific date. In other situations, however, their interest lies in a range of dates. The user might enter the two dates and then expect to view all data that falls between them.

Suppose, for example, that in an Oracle Forms application the user enters 12/4/93 in the start date field and 4/8/96 in the end date field. The query that Oracle Forms executes against the database would need to have logic in it as follows:

hire_date BETWEEN '04-DEC-93' AND '08-APR-96'

or, more generally:

hire_date BETWEEN :criteria.start_date AND :criteria.end_date

where criteria is the name of the block containing the start_date and end_date fields. The colons (:) in front of the field names indicate to PL/SQL that these are bind variables from the host environment.

Sometimes this general logic can be passed directly to the SQL layer. In other situations, programmers must use the Pre-Query trigger or the SET_BLOCK_PROPERTY built-in to alter the SQL statement directly. In this case, they will need to create a string date range from the input dates.

Rather than write the application-specific code to handle this each time, you can build a generic utility, using TO_CHAR and TO_DATE conversion functions.

I offer below the date_range function. Its specification is as follows:

FUNCTION date_range
   (start_date_in IN DATE,
    end_date_in IN DATE,
    check_time_in IN VARCHAR2 := 'NOTIME')
RETURN VARCHAR2

The arguments to date_range are:

start_date_in

The starting date in the range. If NULL then use the min_start_date. If that is NULL, range has form `<= end_date'.

end_date_in

The end date in the range. If NULL then use the max_end_date. If that is NULL, range has form `>= start_date'.

check_time_in

Flag indicating whether or not to include the time component of the dates in the range check. If TIME then use the time component of the dates as part of the comparison. If NOTIME then strip off the time.

If the start date is NULL or the end date is NULL, the string returned by date_range uses the <= and >= operators rather than the BETWEEN operator.

Here are some examples of the output from date_range. Note that date_range places two contiguous quote marks around each date because these are actually string literals. At runtime these two quotes are resolved into a single quote by PL/SQL:

date_range ('04-DEC-93', '08-APR-96')
==>
   BETWEEN TO_DATE (''04-DEC-93'') AND TO_DATE (''08-APR-96'')

date_range ('04-DEC-93', NULL)
==>
   >= TO_DATE (''04-DEC-93'')

date_range (NULL, '04-DEC-93')
==>
   <= TO_DATE (''04-DEC-93'') 

If you do want to include the time component, then date_range will generate a string in this form:

date_range ('04-DEC-93', '08-APR-96', 'time')
==>
   BETWEEN TO_DATE (''04-DEC-93'', ''HHMMYYYY HHMISS'') AND
           TO_DATE (''08-APR-96'', ''HHMMYYYY HHMISS'')

Let's take a look at how you might use date_range in query processing in Oracle Forms. The Pre-Query trigger modifies the Default Where clause of a base table block. In Pre-Query, an assignment actually results in the addition of a WHERE clause in the SQL query for that block. Let's look at a simple example first. If Pre-Query contains a statement like this:

:customer.contact_date := '12-JAN-95';

then the query executed by the forms tool to fill the block contains a WHERE clause that looks like this:

WHERE <other clauses> AND (contact_date = '12-JAN-95')

So a simple assignment results in a straightforward comparison/restriction in the WHERE clause. You can also place complex SQL statements in the WHERE clause by appending a pound sign ( # ) at the beginning of the assignment string. When the forms tools detect the # , they know to substitute completely the equals sign ( = ) comparison with the text following the symbol. So if you place a statement like this in Pre-Query:

:customer.customer_id :=
   '# IN (SELECT customer_id FROM invoice WHERE invoice_total > ' ||
   TO_CHAR (:invoice.amount);

then the query executed by the forms tool to fill the block contains a WHERE clause that looks like this:

WHERE <other-where-clauses> AND
      customer_id IN (SELECT customer_id
                         FROM invoice WHERE invoice_total > 1000)

This very useful feature is documented in the Advanced Oracle Forms Techniques manual from Oracle Corporation. The syntax of a # assignment in Pre-Query for a date range would be like this:

:employee.hire_date :=
   '# BETWEEN ' || TO_CHAR (:criteria.start_date) || ' AND ' ||
   TO_CHAR (:criteria.end_date);

If I were to place literal dates inside this string assignment, then I would need to put two single quotes together in the string wherever I needed one single quote to appear in the actual value placed in that field, as follows:

:employee.hire_date := '# BETWEEN ''01-JAN-93'' AND ''01-DEC-94''';

Now if I apply the date_range function to this Pre-Query context, I have one of the following:

:customer.contact_date :=
   '# ' || date_range (:criteria.start_date, :criteria.end_date);

or:

:customer.contact_date :=
   '# ' || date_range (:criteria.start_date, :criteria.end_date, 'TIME');

In the first call to date_range, I rely on the default value of the check_time_in parameter of NOTIME to ignore the time component of the dates. In the second call, I explicitly request that the time component be included.

The SET_BLOCK_PROPERTY built-in in Oracle Forms offers another method of modifying the DEFAULT WHERE clause of a base table block. It allows you to directly pass a string of SQL syntax, which then replaces the DEFAULT WHERE clause specified at design time in the form. It is a much more structured approach than using the # syntax. In the following two calls to the built-in, I call date_range to generate a date range and attach that date range syntax to the contact_date column. In the second example I apply TRUNC to the contact date so that the time at which the contact_date was entered does not become a factor in the range check:

SET_BLOCK_PROPERTY
   ('customer', DEFAULT_WHERE,
    'contact_date ' ||
    date_range (:criteria.start_date, :criteria.end_date, 'TIME'));

or:

SET_BLOCK_PROPERTY
   ('customer', DEFAULT_WHERE,
    'TRUNC (contact_date) ' ||
    date_range (:criteria.start_date, :criteria.end_date));

Here is the code for the date_range function:

/* Filename on companion disk: daternge.sf */
FUNCTION date_range
   (start_date_in IN DATE,
    end_date_in IN DATE,
    check_time_in IN VARCHAR2 := 'NOTIME')
RETURN VARCHAR2
IS
   /* String versions of parameters to place in return value */
   start_date_int VARCHAR2(30);
   end_date_int VARCHAR2(30);

   /* Date mask for date<->character conversions. */
   mask_int VARCHAR2(15) := 'MMDDYYYY';

   /* Version of date mask which fits right into date range string */
   mask_string VARCHAR2(30) := NULL;

   /* The return value for the function. */
   return_value VARCHAR2(1000) := NULL;
BEGIN
   /*
   || Finalize the date mask. If user wants to use time, add that to
   || the mask. Then set the string version by embedding the mask
   || in single quotes and with a trailing paranthesis.
   */
   IF UPPER (check_time_in) = 'TIME'
   THEN
      mask_int := mask_int || ' HHMISS';
   END IF;
   /*
   || Convert mask. Example:
   ||       If mask is: MMDDYYYY HHMISS
   ||       then mask string is: ', 'MMDDYYYY HHMISS')
   */
   mask_string := ''', ''' || mask_int || ''')';

   /* Now convert the dates to character strings using format mask */
   start_date_int := TO_CHAR (start_date_in, mask_int);
   end_date_int := TO_CHAR (end_date_in, mask_int);

   /* If both start and end are NULL, then return NULL. */
   IF start_date_int IS NULL AND end_date_int IS NULL
   THEN
      return_value := NULL;

   /* If no start point then return "<=" format. */
   ELSIF start_date_int IS NULL
   THEN
      return_value := '<= TO_DATE (''' || end_date_int || mask_string;

   /* If no end point then return ">=" format. */
   ELSIF end_date_int IS NULL
   THEN
      return_value := '>= TO_DATE (''' || start_date_int || mask_string;

   /* Have start and end. A true range, so just put it together. */
   ELSE
      return_value :=
        'BETWEEN TO_DATE (''' || start_date_int || mask_string ||
           ' AND TO_DATE (''' || end_date_int || mask_string;
   END IF;

   RETURN return_value;

END;

14.3.5 Building a Date Manager

The Oracle Server offers the ability to set a default date format for each instance of a database with the NLS_DATE_FORMAT initialization parameter.[ 2 ] Oracle provides a ruthlessly efficient gatekeeper for its RDBMS: there is no way you will ever be able to enter an invalid date into the database. And there are lots of functions that enable you to perform arithmetic on dates once they are in the database. There are, however, obstacles to entering dates efficiently:

[2] This is a big improvement over earlier versions, in which the default date format was hardcoded to be DD-MON-YY. In the Oracle RDBMS Version 6, for example, if your company happened to use something else, you would have to make extensive use of the TO_DATE and TO_CHAR functions (and set the mask on all date fields in your forms applications, as well).

• Oracle tools do not make it easy for users to enter dates. Oracle Forms, for example, insists that when a user enters a date, it must conform to a single date format mask -- either the system-wide default, or an override for that particular item.

• Users cannot enter partial date information and have the rest defaulted by the system. Shouldn't users be able to enter a date in any manner they please? That same date mask demands that you enter every part of the date, even if it is always going to be in the current month or year. It seems to me that users should be able to enter the smallest number of characters needed to specify their date, and let the application figure out the rest.

It is possible with PL/SQL to build a "date manager" that satisfies the above moral imperatives for our users. The rest of this section explores the implementation of a function, dm_convert, which converts a string entered by the user into an actual Oracle date value. It liberates the user from having to know the default/enforced format in the Oracle7 database, because the input can conform to any of a wide variety of formats. The function determines which format applies, and returns the date.