Re: SQL Date Math

Numerics for dates can add value when there is little use for date calculations. To limit undesirable [not necessarily invalid; e.g. to still allow 9999-99-99 or 0000-##-## as special] values there is the CHECK CONSTRAINT. Then as noted, within a VIEW is the option to CAST a derivation to DATE type. Or by using CASE can handle more dynamic variations than just COALESCE of the one-dimensional [within a single date field] NULL value.

In the following example DateOrdr is date data type, but DateShip is numeric(8, 0) to allow for keying on the single [overloaded] field and thus providing multiple answers for the shipping /date/ business rules.

case
when DateShip < 0 then /* Projected Ship Date */
char(DateOrdr - DateShip days, ISO)
when DateShip = 0 then /* No Ship Date needed */
'Customer Pick Up'
when DateShip = 99999999 then /* Backordered */
'Back Ordered '
when DateShip = 88888888 then /* Date missed */
'*** Overdue! ***'
when DateShip IS NULL then /* No Date assigned */
'** Incomplete **'
else
substr(char(DateShip), 1, 4)||'-'||
substr(char(DateShip), 5, 2)||'-'||
substr(char(DateShip), 7, 2)
end as ShipRule

1>
*BEFORE read means to the database, before the database has read the row/record. Assuming no ability to replace the row here with something other than what is really in the record, then it is mostly pointless for most security processing, as what row is being read is unknown to the trigger program, just as it is to the receiving program.
The *AFTER *READ trigger enables the trigger program to intercept what the program is going to see, and has the option of preventing the read by sending an error message to the database; e.g. in response to what data and what user is trying to see that data. There is not however the possibility at this point to either munge or selectively un-munge the data in the record; e.g. no option to blank, encrypt or decrypt, nor compress or decompress row data. Such actions are the domain of functions.

2>
I am almost positive Derived Field results from a Read Trigger will never happen. A program compiled against a file with FLD_CHAR8 which has a Read Trigger casting that to a BIN(4) alternate representation of a date, or to a CHAR(10) representing an *ISO date character string is problematic; i.e. the program will not know what to do with the data. Similarly problematic for the bound record length changing for something like adding fields. To change the data in the compiled-against format, almost surely a function will always be required to cast or otherwise change the data the program will receive; i.e. the function defines the data type and attributes [for a static binding]. With the capability of a read trigger to modify the record, I would expect support contracts for any [system with any] Read Triggers would have to be specially priced for huge dollars or go purely consulting -- but with that, the loss of general support to the reasonable uses/users of the function.
Even limiting the open of such a file for purely dynamic, the metadata [even if scoped to the open versus per record] would require some method to be established where none exists -- perhaps an open trigger calls the read trigger with a special request to establish the metadata.? Then any programs bound to the original definition should fail with level check irrespective of overrides to request otherwise with any mismatch.?
The whole concept seems a bit off; perhaps I just do not see something obvious.? Regardless....
Presenting derived data to a program from a file is already available with User Defined Functions [UDF]; both table and scalar.

Regards, Chuck