NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
D-15
Examples—DATETIME
The following three data types are NOT compatible, even though they are all
DATETIME types:
DATETIME YEAR TO DAY
DATETIME YEAR TO FRACTION(6)
DATETIME HOUR
•
Range and meaning of fields within DATETIME values
•
Range of DATETIME values
A DATETIME value represents a point in time according to the Gregorian calendar
and a 24-hour clock in local civil time (LCT). The range of times that you can
represent is:
January 1, 1 A.D., 00:00:00.000000 (low value)
December 31, 9999, 23:59:59.999999 (high value)
(The supported range includes some dates and times that are not defined in the
Gregorian calendar, such as eleven days in 1583.)
Examples—DATETIME
•
The following statement creates a table with several columns that have a
DATETIME data type.
CREATE TABLE SCHEDULE (EMPLOYEE_ID CHAR(30),
LAST_SCHEDULE_CHG DATETIME YEAR TO
DAY,
START_WORKDAY DATETIME HOUR,
END_WORKDAY DATETIME HOUR,
PRIMARY KEY EMPLOYEE-ID
)
Field Range Bytes* Meaning
YEAR 0001 to 9999 2 Year
MONTH 1 or 01 to 12 1 Month in year
DAY 1 or 01 to 31** 1 Day in month
HOUR 0 or 00 to 23 1 Hour in day
MINUTE 0 or 00 to 59 1 Minute in hour
SECOND 0 or 00 to 59 1 Second in minute
FRACTION 0 to 999999 4 Microsecond in seconds
* Bytes is the number of bytes used to store the field in a column that includes the
field. Data-time columns that allow null values are two-bytes larger than the total of
the included fields.
** The DAY field is also constrained by the month and year, so the number of days in
a month can never exceed the number of days in that specific calendar month.