Default Date Considerations

The default date value the data adapter uses for the RDBMS DATE datatype changed starting with FOCUS Version 6.8. If you use the FOCUS MODIFY facility to maintain RDBMS tables containing DATE columns, this change may have some impact on your applications. If you have a read-only version of the data adapter, or if your site does not use MODIFY, you will not be affected. You can control the default date value with the data adapter SET DEFDATE command.

In this section: The Default Date Value The Data Adapter SET DEFDATE Command Effects of DEFDATE on Existing Applications Chart: FOCUS Date Values for User Input Values

The Default Date Value

The data adapter uses the default date value in conjunction with RDBMS DATE columns described in a Master File as ACTUAL=DATE. Under certain circumstances, if your MODIFY procedure does not provide a value for a DATE column, the data adapter substitutes the default value. In FOCUS Version 6.8 and up, the default value changed to make data adapter date behavior more closely resemble that of the FOCUS DBMS.

In releases of FOCUS prior to 6.8, the data adapter default value for RDBMS tables was '1901-01-01' (for the sake of convenience, all DATE values are in DB2 ISO format unless otherwise indicated). The FOCUS DBMS default (or base) value has always been '1900-12-31'.

With the FOCUS DBMS, base date values print as blanks in report output by default. (This discussion assumes that the FOCUS DATEDISPLAY parameter is OFF, the default. The SET DATEDISPLAY = ON command displays the base date in FOCUS reports. See your FOCUS documentation for details.) The old data adapter default DATE value always displayed on reports.

When the DEFDATE value is NEW, the data adapter base date value is identical to the FOCUS DBMS base date: 1900-12-31, and it displays the same way in reports.

The Data Adapter SET DEFDATE Command

You can control the data adapter default date with the following data adapter SET command

SQL [sqlengine] SET DEFDATE {NEW|OLD}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

OLD

The data adapter supplies the old default date, '1901-01-01'. OLD was the default in FOCUS Version 6.5.

NEW

The data adapter supplies the new default date, '1900-12-31'. NEW is the default starting in FOCUS Version 6.8.

Effects of DEFDATE on Existing Applications

With the new default date, TABLE requests no longer require DEFINE or COMPUTE commands in order to display blanks instead of default dates. Applications that test for default values require updating to reflect the new default date.

For the sake of consistency, you may wish to update your databases to change old default values to the new default. You can use the following code to change old default values

SQL [sqlengine] UPDATE creator.tablename
    SET date_column = '1900-12-31' WHERE date_column = '1901-01-01'
END

where:

sqlengine

Is DB2, SQLDS, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

The change in the default date value affects only the selection of default values supplied by MODIFY procedures under certain circumstances, and whether default values appear on a FOCUS report. In all other respects (for example, in screening conditions), the default value is a valid date value. If you use a report writer other than FOCUS, any default date values in the RDBMS table will display as either '1901-01-01' or '1900-12-31', depending on the FOCUS release that placed them in the table and the value of the DEFDATE parameter.

Chart: FOCUS Date Values for User Input Values

The following chart summarizes how FOCUS stores date values in response to specific user input values for non-conditional and conditional data entry in MODIFY:

				If DEFDATE is NEW:
MISSING	User Input	FOCUS DBMS Report Output	OLD RDBMS Report Output	RDBMS Report Output	Value Stored in RDBMS
Non-conditional data entry (<date_column>) in MODIFY
ON	blank	blank	1901/01/01	blank	1900-12-31
ON	'.'	NODATA	NODATA	NODATA	null
ON	0 (zero)	blank	1901/01/01	blank	1900-12-31
OFF	blank	blank	1901/01/01	blank	1900-12-31
OFF	'.'	input value rejected by MODIFY
OFF	0 (zero)	blank	1901/01/01	blank	1900-12-31
Conditional data entry (<date_column) in MODIFY
ON	blank	NODATA	NODATA	NODATA	null
ON	'.'	NODATA	NODATA	NODATA	null
ON	0 (zero)	blank	1901/01/01	blank	1900-12-31
OFF	blank	blank	1901/01/01	blank	1900-12-31
OFF	'.'	input value rejected by MODIFY
OFF	0 (zero)	blank	1901/01/01	blank	1900-12-31

Note:

• FOCUS displays the NODATA value whenever a column contains a null value. The default NODATA value is the period ('.').
• Column 1 shows the MISSING parameter in the Master File for the column described as ACTUAL=DATE.
• Column 2 shows the value the MODIFY user enters for that column.
• Column 3 shows, for the FOCUS DBMS, how that value would display in FOCUS report requests. If a blank appears on the report, the stored value is '1900-12-31'.
• Column 4 shows, for an RDBMS table, how the entered value displays in a FOCUS report if DEFDATE is OLD. You can see the value FOCUS stored, since it appears on the report instead of a blank.
• Column 5 shows, again for an RDBMS table, how the entered value displays on a FOCUS report provided DEFDATE is NEW. The report output is now identical to the report produced for the FOCUS DBMS (Column 3).
• Column 6 shows the actual default value stored in the RDBMS table for values entered when DEFDATE is NEW.

The chart shows how values are stored and displayed for conditional (single caret) and unconditional (double caret) fields.

Note: If you enter a blank for a conditional field, and if a value already exists for that field, the field is not updated. The chart shows what happens when you enter blanks for conditional fields that have no prior values.

If you want to be sure that FOCUS does not store a default value regardless of the user's input, have the application program check the entered value. If the user enters 0 (zero) or blank, COMPUTE the date field as 'MISSING' to make FOCUS set the column to NULL in UPDATE or INSERT statements. This technique works only if the date column allows nulls and is described to FOCUS as MISSING=ON.

See your FOCUS documentation if you are not familiar with the terms in the preceding discussion.