NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
I-22
Examples—INTERVAL Data Type
You can also determine the storage size for a column by querying the COLSIZE
column of the COLUMNS catalog table. For example, the following query from
SQLCI returns a column's length in bytes:
>>SELECT colsize FROM columns
+> WHERE tablename LIKE "%table-name%"
+> AND colname = "column-name";
* Version Management Consideration
The INTERVAL data type is supported on NonStop SQL/MP versions 2 and later.
Examples—INTERVAL Data Type
•
The following statement creates a table in which three of the four columns are of
data type INTERVAL. Column AGE represents an interval of years and months (for
example 27-2, which means 27 years and 2 months), column YRS_EXPERIENCE
represents an interval of years, and column HOURS_VACATION represents an
interval of hours.
CREATE TABLE EMPLOYEE (
AGE INTERVAL YEAR TO MONTH,
NAME PIC X(30) NO DEFAULT NOT NULL,
YRS_EXPERIENCE INTERVAL YEAR,
HOURS_VACATION INTERVAL HOUR(3) NOT NULL
)
YRS_EXPERIENCE can be no more than 99 (the default is two digits), but
HOURS_VACATION can be up to 999 because the CREATE TABLE statement
explicitly specifies three digits.
INTERVAL Literals
An INTERVAL literal is a constant of data type INTERVAL that represents a positive or
negative duration of time as a year-month or day-time interval.
An INTERVAL literal can contain a maximum of 18 digits, plus characters such as
hyphens (-) or colons (:) that separate the values of INTERVAL fields. The value can be
enclosed in either double quotation marks (shown in the following diagram) or in single
quotation marks.
[ - ] INTERVAL { "y-m" } start-field [ (digits) ]
{ "d-t" }
[ TO end-field]