Communicator 3000 MPE/iX Release 6.0 (Platform Software Release C.60.00) (30216-90269)
178 Chapter 10
Technical Articles
New Features in ALLBASE/SQL Version G3
Solution
When YY is specified in the format specification of either TO_DATE or
TO_DATETIME, and if its value of the year specified is less than 50, then
the century part of the DATE or DATETIME defaults to 20, otherwise it is
set to 19. This behavior can be overridden by setting the JCW
HPSQLsplitcentury to a value between 0 and 100. If the year
specified is less than the value of the JCW HPSQLsplitcentury then
the century part is set to 20, otherwise it is set to 19.
For the above example,
Case 1: HPSQLsplitcentury is not set
TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59
Case 2: HPSQLsplitcentury is set to 0
TO_DATE('12/31/35','MM/DD/YY') = 1935-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 1935-12-31 23:59:59
Case 3: HPSQLsplitcentury is set to 70
TO_DATE('12/31/35','MM/DD/YY') = 2035-12-31
TO_DATETIME('351231235959','YYMMDDHHMISS') = 2035-12-31 23:59:59
CAST Function
With the G1.15 release, the CAST function enhancement for
ALLBASE/SQL and IMAGE/SQL became available to customers. The
CAST function is used to explicitly convert data from one data type to
another. The CAST function not only allows conversion between
compatible data types, such as between CHAR and BINARY or between
INTEGER and DECIMAL, but it will also allow conversion between
certain normally incompatible types, such as between CHAR and
INTEGER.
The CAST function is defined in the ANSI SQL2 standard. CAST in
ALLBASE/SQL and IMAGE/SQL complies with that standard. In
addition, several extensions to the standard specification have been
added to make CAST even more powerful.
The CAST function can be used anywhere a general expression is
allowed. Also, as a part of this enhancement, the SQL parser has been
enhanced to allow general expressions in more of the SQL syntax. For
example, general expressions including nested functions are now
allowed in all the date/time functions and string functions. Therefore,
CAST will be supported inside functions that support expressions
including aggregate functions. CAST will also take general expressions
including nested functions as input.