To facilitate the handling of date and time calculations, several functions are available which compute with values of these types.
A reservation date increased by two days gives:
SELECT arrival, ADDDATE (arrival,2) arrival2, rno
FROM reservation
WHERE rno = 130
The number of reservation days between arrival and departure gives:
SELECT arrival, departure, DATEDIFF (arrival, departure)
difference, rno
FROM reservation
WHERE rno = 130
Additional date functions are:
SUBDATE computes a past date
DAYOFWEEK indicates the day of week (first day: Monday)
DAYOFMONTH indicates the number of the day of month
DAYOFYEAR indicates the number of the day of year
WEEKOFYEAR indicates the number of the week of year
for the specified day
YEAR, MONTH, DAY extract the year, month or day from a date or
timestamp value
MAKEDATE forms a date value from a year and a day
DAYNAME displays the day of week as a character string
MONTHNAME displays the name of month as a character string
The corresponding time functions are:
ADDTIME
SUBTIME
TIMEDIFF
HOUR
MINUTE
SECOND
MICROSECOND
MAKETIME forms a time value out of three significant numbers
TIMESTAMP forms a timestamp value consisting of a date, a time value and 0 micro seconds
DATE forms a date value
TIME forms a time value
Different date formats are available for the processing of date values. The keywords ISO, USA, EUR, JIS or INTERNAL can be used to determine that the date is to be represented either according to the ISO standard or in US American, European, Japanese or the database internal format (see the ADABAS Reference Manual).