The FX element

PL/SQL offers the FX (format exact) element as a modifier to a format mask. FX 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 part of a date value, except to delimit separate parts of the date and time:

TO_DATE ('Jan 15 1994', 'MON DD YYYY')

· 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')TO_DATE ('7/16/94', 'MM/DD/YY')

· Punctuation in the string to be converted can simply match the length and position of punctuation in the format. In the following example, my format mask specifies hyphen (-) delimiters, and the string that I pass it uses caret (^) delimiters. Even so, TO_DATE has no problem making the match. For example:

TO_DATE ('JANUARY^1^94', 'Month-dd-yy')

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 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 in the 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 but the first of the following examples:

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

PL/SQL raises one of the following errors:

ORA-01861: literal does not match format stringORA-01862: the numeric value does not match the length of the format item

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. So the first time FX appears in the format, it turns on exact matching for any following elements. The second time it appears, it indicates that an exact match is not required for any following elements, and so on.

The following example specifies 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')

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 can use FM (fill mode) 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. Later in this chapter, in Section 10.2.3.3, you'll see how the FM modifier can strip leading blanks and zeros from the output of a call to TO_CHAR.

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.