Neoview SQL Reference Manual (R2.5)

Datetime Data Types
A value of datetime data type represents a point in time according to the Gregorian calendar
and a 24-hour clock in local civil time (LCT). A datetime item can represent a date, a time, or a
date and time.
When a numeric value is added to or subtracted from a date type, the numeric value is
automatically CASTed to an INTERVAL DAY value. When a numeric value is added to or
subtracted from a time type or a timestamp type, the numeric value is automatically CASTed to
an INTERVAL SECOND value. For information on CAST, see “CAST Expression” (page 357).
Neoview SQL accepts dates, such as October 5 to 14, 1582, that were omitted from the Gregorian
calendar. This functionality is a Neoview SQL extension.
The range of times that a datetime value can represent is:
January 1, 1 A.D., 00:00:00.000000 (low value) December 31, 9999, 23:59:59.999999 (high value)
Neoview SQL has three datetime data types:
datetime-type is:
DATE
| TIME [(time-precision)]
| TIMESTAMP [(timestamp-precision)]
DATE
specifies a datetime column that contains a date in the external form yyyy-mm-dd and stored
in four bytes.
TIME [(time-precision)]
specifies a datetime column that, without the optional time-precision, contains a time in the
external form hh:mm:ss and is stored in three bytes. time-precision is an unsigned integer
that specifies the number of digits in the fractional seconds and is stored in four bytes. The
default for time-precision is 0, and the maximum is 6.
TIMESTAMP [(timestamp-precision)]
specifies a datetime column that, without the optional timestamp-precision, contains a
timestamp in the external form yyyy-mm-dd hh:mm:ss and is stored in seven bytes.
timestamp-precision is an unsigned integer that specifies the number of digits in the
fractional seconds and is stored in four bytes. The default for timestamp-precision is 6,
and the maximum is 6.
Considerations for Datetime Data Types
Datetime Ranges
The range of values for the individual fields in a DATE, TIME, or TIMESTAMP column is specified
as:
Year, from 0001 to 9999
yyyy
Month, from 01 to 12
mm
Day, from 01 to 31
dd
Hour, from 00 to 23
hh
Minute, from 00 to 59
mm
Second, from 00 to 59
ss
Microsecond, from 000000 to 999999
msssss
When you specify datetime_value (FORMAT ‘string’) in the DML statement and the
specified format is ‘mm/dd/yyyy’,’MM/DD/YYYY’, or ‘yyyy/mm/dd’ or ‘yyyy-mm-dd’, the
datetime type is automatically cast.
250 SQL Language Elements