|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).