DATES - caveats with date arithmetic

Some date expressions are not supported by DB2. Here are some things to be aware of:
 
You cannot use any arithmetic operators in DB2 date expression except for a single plus or minus (+ or -). In addition, stricter type checking enforced by DB2 requires that explicit conversions be used in Access in some cases.
 
Invalid
1)   Date() + 90
2)   [Now() - 120
3)   Date() + 60 + 30
4)   Date() + 30 * 3
 
Valid
(only when the DB2 receiving column is of type TIMESTAMP since DateAdd returns a TIMESTAMP)
1)   DateAdd (“d”, 90, Date())
2)   DateAdd (“h”, -72, Now())
3)   DateAdd (“d”, 90, Date())
4)   DateAdd (“d”, 90, Date())
 
Valid
(when the DB2 receiving column is of type DATE)
CDate(DateAdd (“d”, 90, Date()) )

When NOT using the UDF extensions:

Invalid
DateAdd(“q”, 3, Date())
There is no DB2 equivalent for Quarters; use Months instead
The following are Table Validation Rules:
1)   [Date Time]>=DateAdd(“d”,-14,[Date Only]) is substituted with DATE_TIME>=DATE_ONLY - 14 DAYS resulting in a data type mismatch
2)   [Date Only]<=DateAdd (“h”, -72, Now()) is substituted with DATE_ONLY<=DATE_TIME - 72 HOURS resulting in a data type mismatch
 
Valid

1)   CDate([Date Time])>=DateAdd(“d”,-14,[Date Only]) is substituted with DATE(DATE_TIME)>=DATE_ONLY-14 DAYS

2)   [Date Only] <=CDate(DateAdd (“h”, -72, Now())) is substituted with DATE_ONLY<=DATE(DATE_TIME - 72 HOURS)