In the following sections, references to datetime values having "character string" representations have been changed to "string" representations. DB2 now supports, for Unicode databases only, "graphic string" representations of datetime values.
Values whose data types are DATE, TIME, or TIMESTAMP are represented in an internal form that is transparent to the user. Date, time, and timestamp values can, however, also be represented by strings. This is useful because there are no constants or variables whose data types are DATE, TIME, or TIMESTAMP. Before it can be retrieved, a datetime value must be assigned to a string variable. The CHAR function or the GRAPHIC function (for Unicode databases only) can be used to change a datetime value to a string representation. The string representation is normally the default format of datetime values associated with the country/region code of the database, unless overridden by specification of the DATETIME option when the program is precompiled or bound to the database.
No matter what its length, a large object string, a LONG VARCHAR value, or a LONG VARGRAPHIC value cannot be used to represent a datetime value (SQLSTATE 42884).
When a valid string representation of a datetime value is used in an operation with an internal datetime value, the string representation is converted to the internal form of the date, time, or timestamp value before the operation is performed.
Date, time and timestamp strings must contain only characters and digits.
The definitions of these terms have been changed slightly. References to "character string" representations have been changed to "string" representations.
DATE, TIME, and TIMESTAMP can now be cast to GRAPHIC and VARGRAPHIC. GRAPHIC and VARGRAPHIC can now be cast to DATE, TIME, and TIMESTAMP. Graphic string support is only available for Unicode databases.
There is now data type compatibility for assignments and comparisons between graphic strings and DATE, TIME, and TIMESTAMP values. Graphic string support is only available for Unicode databases.
The basic rule for datetime assignments is that a DATE, TIME, or TIMESTAMP value can only be assigned to a column with a matching data type (whether DATE, TIME, or TIMESTAMP) or to a fixed- or varying-length string variable or string column. The assignment must not be to a LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB, or BLOB variable or column.
When a datetime value is assigned to a string variable or string column, conversion to a string representation is automatic. Leading zeros are not omitted from any part of the date, time, or timestamp. The required length of the target will vary, depending on the format of the string representation. If the length of the target is greater than required, and the target is a fixed-length string, it is padded on the right with blanks. If the length of the target is less than required, the result depends on the type of datetime value involved, and on the type of target.
When the target is a host variable, the following rules apply:
If ISO or JIS formats are used, and if the length of the host variable is less than 8 characters, the seconds part of the time is omitted from the result and assigned to the indicator variable, if provided. The SQLWARN1 field of the SQLCA is set to indicate the omission.
>>-DATE--(--expression--)--------------------------------------><
The schema is SYSIBM.
The DATE function returns a date from a value.
The argument must be a date, timestamp, a positive number less than or equal to 3 652 059, a valid string representation of a date or timestamp, or a string of length 7 that is not a LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB, or BLOB.
Only Unicode databases support an argument that is a graphic string representation of a date or a timestamp.
If the argument is a string of length 7, it must represent a valid date in the form yyyynnn, where yyyy are digits denoting a year, and nnn are digits between 001 and 366, denoting a day of that year.
The result of the function is a date. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
Examples:
Assume that the column RECEIVED (timestamp) has an internal value equivalent to '1988-12-25-17.12.30.000000'.
DATE(RECEIVED)
DATE('1988-12-25')
DATE('25.12.1988')
DATE(35)
>>-GRAPHIC--(--graphic-expression--+------------+--)-----------><
'-,--integer-'
The schema is SYSIBM.
The GRAPHIC function returns a GRAPHIC representation of a graphic string type or a GRAPHIC representation of a datetime type.
The result of the function is a GRAPHIC. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Datetime to Graphic:
>>-GRAPHIC--(--datetime-expression--+--------------+--)--------><
'-,--+-ISO---+-'
+-USA---+
+-EUR---+
+-JIS---+
'-LOCAL-'
The code page of the string is the code page of the database at the application server.
>>-TIME--(--expression--)--------------------------------------><
The schema is SYSIBM.
The TIME function returns a time from a value.
The argument must be a time, timestamp, or a valid string representation of a time or timestamp that is not a LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB, or BLOB.
Only Unicode databases support an argument that is a graphic string representation of a time or a timestamp.
The result of the function is a time. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
Example:
SELECT * FROM IN_TRAY
WHERE TIME(RECEIVED) >= CURRENT TIME + 1 HOUR
>>-TIMESTAMP--(--expression--+-------------+--)----------------><
'-,expression-'
The schema is SYSIBM.
The TIMESTAMP function returns a timestamp from a value or a pair of values.
Only Unicode databases support an argument that is a graphic string representation of a date, a time, or a timestamp.
The rules for the arguments depend on whether the second argument is specified.
A string of length 14 must be a string of digits that represents a valid date and time in the form yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds.
The result of the function is a timestamp. If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The other rules depend on whether the second argument is specified:
Example:
TIMESTAMP(START_DATE, START_TIME)
Returns the value '1988-12-25-17.12.30.000000'.
Character to Vargraphic:
>>-VARGRAPHIC--(--character-string-expression--)---------------><
Datetime to Vargraphic:
>>-VARGRAPHIC--(--datetime-expression--)-----------------------><
Graphic to Vargraphic:
>>-VARGRAPHIC--(--graphic-string-expression--+------------+----->
'-,--integer-'
>--)-----------------------------------------------------------><
The schema is SYSIBM.
The VARGRAPHIC function returns a graphic string representation of a:
The result of the function is a varying length graphic string (VARGRAPHIC data type). If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Character to Vargraphic
The length attribute of the result is equal to the length attribute of the argument.
Let S denote the value of the character-string-expression. Each single-byte character in S is converted to its equivalent double-byte representation or to the double-byte substitution character in the result; each double-byte character in S is mapped 'as-is'. If the first byte of a double-byte character appears as the last byte of S, it is converted into the double-byte substitution character. The sequential order of the characters in S is preserved.
The following are additional considerations for the conversion.
Datetime to Vargraphic
Graphic to Vargraphic
If the length of the graphic-string-expression is greater than the length attribute of the result, truncation is performed and a warning is returned (SQLSTATE 01004), unless the truncated characters were all blanks and the graphic-string-expression was not a long string (LONG VARGRAPHIC or DBCLOB).