版本注意事項

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

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

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

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

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

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

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

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

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

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

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

[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]