Release Notes

|43.10 GRAPHIC type and DATE/TIME/TIMESTAMP compatibility

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

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

|43.10.1.1 Date strings, time strings, and datetime strings

|The definitions of these terms have been changed slightly. |References to "character string" representations have been changed to "string" |representations.

|43.10.2 Casting between data types

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

|43.10.3 Assignments and comparisons

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

|43.10.4 Datetime assignments

|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: |

|DATE: If the variable length is less than 10 characters, |an error occurs.
|TIME: If the USA format is used, the length of the |variable must not be less than 8 characters; in other formats the length |must not be less than 5 characters.
|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.
|TIMESTAMP: If the host variable is less than 19 |characters, an error occurs. If the length is less than 26 characters, |but greater than or equal to 19 characters, trailing digits of the |microseconds part of the value are omitted. The SQLWARN1 field of the |SQLCA is set to indicate the omission. |

|43.10.5 DATE

| | |

|>>-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: |

|If the argument is a date, timestamp, or valid string representation of a |date or timestamp: |
- |The result is the date part of the value. |
|If the argument is a number: |
- |The result is the date that is n-1 days after January 1, 0001, |where n is the integral part of the number. |
|If the argument is a string with a length of 7: |
- |The result is the date represented by the string. |
|

|Examples:

|Assume that the column RECEIVED (timestamp) has an internal value |equivalent to |'1988-12-25-17.12.30.000000'. |

|This example results in an internal representation of |'1988-12-25'.
```
|   DATE(RECEIVED)
```
|This example results in an internal representation of |'1988-12-25'.
```
|   DATE('1988-12-25')
```
|This example results in an internal representation of |'1988-12-25'.
```
|   DATE('25.12.1988')
```
|This example results in an internal representation of |'0001-02-04'.
```
|   DATE(35)
```
|

|43.10.6 GRAPHIC

| | |

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

|graphic-expression: |An expression that returns a value that is a graphic |string.
|integer: |An integer value specifying the length attribute of the resulting GRAPHIC |data type. The value must be between 1 and 127. If |integer is not specified, the length of the result is the same as the |length of the first argument. |

|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-'
| 
|

|Datetime to Graphic

|datetime-expression

|An expression that is one of the following three data types |

|date: |The result is the graphic string representation of the date in the format |specified by the second argument. The length of the result is |10. An error occurs if the second argument is specified and is not a |valid value (SQLSTATE 42703).
|time: |The result is the graphic string representation of the time in the format |specified by the second argument. The length of the result is 8. |An error occurs if the second argument is specified and is not a valid value |(SQLSTATE 42703).
|timestamp: |The second argument is not applicable and must not be specified (SQLSTATE |42815). The result is the graphic string representation of the |timestamp. The length of the result is 26. |

|The code page of the string is the code page of the database at the |application server. |

|43.10.7 TIME

| | | | | |

|>>-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: |

|If the argument is a time: |
- |The result is that time. |
|If the argument is a timestamp: |
- |The result is the time part of the timestamp. |
|If the argument is a string: |
- |The result is the time represented by the string. |
|

|Example: |

|Select all notes from the IN_TRAY sample table that were received at |least one hour later in the day (any day) than the current time.
```
|   SELECT * FROM IN_TRAY
|     WHERE TIME(RECEIVED) >= CURRENT TIME + 1 HOUR
```
|

|43.10.8 TIMESTAMP

| | | | | |

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

|If only one argument is specified: |
- |It must be a timestamp, a valid string representation of a timestamp, or a |string of length 14 that is not a LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB, |or BLOB.
  |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. |
|If both arguments are specified: |
- |The first argument must be a date or a valid string representation of a |date and the second argument must be a time or a valid string representation |of a time. |
|

|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: |

|If both arguments are specified: |
- |The result is a timestamp with the date specified by the first argument |and the time specified by the second argument. The microsecond part of |the timestamp is zero. |
|If only one argument is specified and it is a timestamp: |
- |The result is that timestamp. |
|If only one argument is specified and it is a string: |
- |The result is the timestamp represented by that string. If the |argument is a string of length 14, the timestamp has a microsecond part of |zero. |
|

|Example: |

|Assume the column START_DATE (date) has a value equivalent to |1988-12-25, and the column START_TIME (time) has a value equivalent to |17.12.30.
```
|   TIMESTAMP(START_DATE, START_TIME)
```
|Returns the value |'1988-12-25-17.12.30.000000'. |

|43.10.9 VARGRAPHIC

| | | | | | | |

|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: |

|character string value, converting single byte characters to double byte |characters,
|datetime value (only supported on Unicode databases)
|graphic string value, if the first argument is any type of graphic |string. |

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

|character-string-expression: |An expression whose value must be of a character string data type other |than LONG VARCHAR or CLOB, and whose maximum length must not be greater than |16 336 bytes. |

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

|For a Unicode database, this function converts the character string from |the code page of the operand into UCS-2. Every character of the |operand, including DBCS characters, is converted. If the second |argument is given, it specifies the desired length (number of UCS-2 |characters) of the resulting UCS-2 string.
|The conversion to double-byte code points by the VARGRAPHIC function is |based on the code page of the operand.
|Double-byte characters of the operand are not converted. All other |characters are converted to their corresponding double-byte depiction. |If there is no corresponding double-byte depiction, the double-byte |substitution character for the code page is used.
|No warning or error code is generated if one or more double-byte |substitution characters are returned in the result. |

|Datetime to Vargraphic |

|datetime-expression: |An expression whose value must be of the DATE, TIME, or TIMESTAMP data |type. |

|Graphic to Vargraphic |

|graphic-string-expression: |An expression that returns a value that is a graphic string.
|integer: |The length attribute for the resulting varying length graphic |string. The value must be between 0 and 16 336. If |this argument is not specified, the length of the result is the same as the |length of the argument. |

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]