Date/time arithmetic in DP4 SQL attempts to follow the IBM SAA standard. Unfortunately, the wording of the standard in this area is obscure, so it may not have been implemented exactly as its authors intended.
The ANSI standard does not support the date or time data types
The following describes date arithmetic; time arithmetic is similar.
Date constants are written in the form dd.mm.yy or dd.mm.yyyy and time constants as hh:mm or hh:mm:ss.However if you are using a third party product such as Microsoft Access to access DP4 you will need to use that product's date or time syntax in SQL statements. For example Access expects dates to be written in the form #26/04/2002# or #26 May 2002#. Although this format is not acceptable to DP4 it will work because Access does not submit dates as literals, but as ? parameters.
There are no date or time constants in the SAA standard
An integer constant followed by one of the words DAYS, MONTHS or YEARS is called a labelled_duration. The corresponding words for times are HOURS, MINUTES and SECONDS. The singular forms of these words are also accepted.
A date_duration is the result of subtracting one date from another. They should be interpreted as yyyymmdd, where yyyy represents the number of years, mm the number of months, and dd the number of days. For example, the result of:
order_date-31.12.1993
is 1012, where ORDER_DATE equals the 12th November 1994. A duration of 0 years, 10 months and 12 days.
The operations possible on dates are as follows:
date + date_duration or labelled_duration
date - date_duration or labelled_duration
giving a date result, or
date - date
string - date
date - string
giving a date_duration.
If a number of months is added or subtracted, and the resulting date is illegal, the day is adjusted downwards; for example, the result of adding 1 month to 31.01.1989 is 28.02.1989.
Subtracting a date from a character string (or the reverse) is the same as subtracting one date from another. The character string is automatically converted into a date and the result is returned as a date_duration.
Adding or subtracting a date_duration or labelled_duration to or from a date has the same effect as separately adding or subtracting the corresponding number of years, months and days (in that order).
The following functions can be used:
|
Function |
Action |
|
DATE(number) |
Converts number into a date; 1 becomes 01.01.0001 |
|
DATE(string) |
Converts a string into a date. The string must be exactly in the date format given by the user's current preference settings. |
|
DATE(date) |
Returns its argument unchanged |
|
TIME(string) |
Converts a string into a time |
|
TIME(time) |
Returns its argument unchanged |
|
DAY(date) |
Returns the day part of its argument, which can be a date or a date duration |
|
MONTH(date) |
Performs the corresponding operation in the same way as the DAY function |
|
YEAR(date) |
As MONTH |
|
HOUR(time) |
As MONTH |
|
MINUTE(time) |
As MONTH |
|
SECOND(time) |
As MONTH |
|
DAYS(date) |
Returns the number of days since 01.01.0001 |
|
DAYS(string) |
Converts the string into a date, and returns the number of days since 01.01.0001 |
|
CHAR(date/time) |
Converts its argument into a character string. An optional second argument specifies the format to be used; this may be one of the following: |
|
EUR |
IBM European Standard |
|
ISO |
ISO Standard |
|
JIS |
Japanese Industrial Standard |
|
USA |
IBM USA standard |
|
|
The default is to use the current preference settings |
For example, the expression:
DAYS(date1) - DAYS(date2)
returns the number of days between date1 and date2, whereas:
DAY(date1 - date2)
expresses the difference between the dates in the form YYYYMMDD and returns the day part of the result.
Where a character string is automatically converted to a date (in date subtraction, or in the argument to the DAYS function) the conversion is made as though the DATE function had been used.
Date or time values may be assigned to character fields, and character values can be assigned to date fields; an automatic conversion is performed as though the DATE or CHAR function had been called.