ALLBASE/SQL Reference Manual (36216-90216)
Chapter 8 249
Expressions
Date/Time Functions
./:., Punctuation marks reproduced in value (includes spaces)
"string" Quoted string reproduced in value
Delimiting punctuation marks must be the same in the value parameter and the format
specification parameter.
• The TO_DATETIME function and the TO_CHAR function on TIME expressions use the
date/time default format 'YYYY-MM-DD HH:MI:SS.FFF'.
In addition to all formats shown for the date and time format specifications above, the
following are also allowed for date/time formats (made up of the numeric characters 0
through 9):
F Tenth of a second (.0 to .9)
FF Hundredth of a second (.00 to .99)
FFF Thousandth of a second (.000 to .999)
• The TO_INTERVAL function and the TO_CHAR function on INTERVAL expressions
use the interval default format 'DAYS HH:MI:SS.FFF'.
The following formats are allowed in an interval format specification:
DAYS MI SECONDS FFF
ZDAYS ZMI ZSECONDS -/:.,
HH or HH24 SS F "string"
ZHH or ZHH24 ZSS FF
These were described in the TIME and DATETIME format specifications above.
• Literals for date/time data types which do not specify all elements of the date/time
value are expanded and filled as described below:
• INTERVAL is zero filled on the left and the right.
• DATE, TIME, and DATETIME are left-filled with the current values from the
system clock, and right-filled with appropriate portions of the default '0000-01-01
00:00:00.000'.
• When YY is specified in the
FormatSpecification
and if its value in
StringExpression
is less than 50, then the century part of DATE and DATETIME
defaults to 20, else it is set to 19. This behavior can be overridden by setting the
environment variable HPSQLsplitcentury to a value between 0 and 100. If the YY part
is less than the value of environment variable HPSQLsplitcentury then the century
part is set to 20, else it is set to 19.
• Output values are truncated, not rounded, to fit in the specified format.
• The TO_INTEGER format specification is not optional, and must consist of one of the
following single elements only:
CC MM DAYS SS
YYYY DDD HH or HH24 SECONDS
YY DD HH12 F, FF, or FFF
Q D MI
• ADD_MONTHS is a related function. ADD_MONTHS adds a duration of months to a