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