x

The 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 adapter substitutes the default value. In FOCUS Version 6.8 and up, the default value changed to make adapter date behavior more closely resemble that of the FOCUS DBMS.

In releases of FOCUS prior to 6.8, the 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 adapter default DATE value always displayed on reports.

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

x

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

{ENGINE|SQL} [sqlengine] SET DEFDATE {NEW|OLD}

where:

sqlengine

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

OLD

The adapter supplies the old default date, '1901-01-01'.

NEW

The adapter supplies the new default date, '1900-12-31'. NEW is the default value.

x

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,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.

x

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	UserInput	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.