Sample data conversions
The tables in this topic provide sample character, numeric, date, time, and timestamp conversions.
These tables use the following standard information:
- In the Data input and Data column values:
- Character strings are depicted as 'string'.
- The letter b represents a blank in the character strings.
- Hexadecimal strings are depicted as X'hexString'.
- Noncharacter input and column values are depicted as hexadecimal strings.
- In the Data column values:
- All column values are depicted in non-normalized DB2 internal format.
- All columns are defined NOT NULL and no column values contain the null indicator.
The following table is a sample of character data conversions:
Data type input | Data type column | Data input | Data column | Comments |
|---|---|---|---|---|
CHAR | CHAR(8) | 'ABCDEFbb' | 'ABCDEFbb' | Input length defaults to column length |
CHAR(6) | CHAR(8) | 'ABCDEF' | 'ABCDEFbb' | Result padded with blanks |
CHAR(8) | VARCHAR(8) | 'ABCDEFbb' | X'0008','ABCDEFbb' or 'ABCDEFbb' | Basic row format or Reordered row format |
CHAR(8) TRIM | VARCHAR(6) | 'ABCDEFbb' | X'0006','ABCDEF' or 'ABCDEF' | Basic row format or Reordered row format Trailing blanks truncated |
CHAR(8) | VARCHAR(6) | 'ABCDEFbb' | Conversion error | Target too small |
VARCHAR | CHAR(8) | X'0004','ABCD' | 'ABCDbbbb' | Result padded with blanks |
VARCHAR | VARCHAR(6) | X'0008','ABCDEFGH' | Conversion error | Target too small |
The following is the sample of numeric data conversions:
Data type input | Data type column | Data input | Data column | Comments |
|---|---|---|---|---|
INTEGER EXTERNAL(10) | INTEGER | 'bbbbbbbb10' | X'0000000A' |
|
INTEGER EXTERNAL(10,-1) | INTEGER | 'bbbb10bbbb' | X'00000064' | Result = 100 |
INTEGER EXTERNAL(10) | INTEGER | '9999999999' | Conversion error | Result larger than maximum integer |
INTEGER | INTEGER(,2) | X'00000064' | X'00000001' | Input value = 100 Result = 1 |
INTEGER1 | CHAR(10) | X'00000064' | 'bbbbbbb100' | Input value = 100 |
INTEGER(,2) | DECIMAL(5,2) | X'0000007D' | X'00125C' | Input value = 125 Result = 1.25 |
DECIMAL EXTERNAL | DECIMAL(5,2) | '12345' | X'12345C' | Result = 123.45 |
DECIMAL EXTERNAL(7,-2) | DECIMAL(7,2) | 'bb12345' | X'1234500C' | Result = 12345.00 |
DECIMAL EXTERNAL(10,4) | DECIMAL(8,2) | '12345678.b' | Conversion error | Target too small |
DECIMAL EXTERNAL(5,1) | INTEGER | 'bb15b' | X'00000001' | Fraction truncated |
DECIMAL EXTERNAL(5,2) | DECIMAL(5,2) | '-1234' | X'01234D' | Result = -12.34 |
DECIMAL EXTERNAL(5,1) ROUND | DECIMAL(5) | 'bbb15' | X'00002C' | Result = 2 due to rounding |
DECIMAL ZONED(4,0) | DECIMAL(4,0) | X'F1F2F3D3' | X'01233D' | Result = -1233. |
DECIMAL ZONED(4,0) | DECIMAL(4,0) | X'F1F2F3C4' | X'01234C' | Result = 1234. |
DECIMAL | DECIMAL(5,2) | X'12345C' | X'12345C' | Input and result = 123.45 |
DECIMAL(5,2) | DECIMAL(4,2) | X'12345C' | Conversion error | Target too small |
FLOAT(53) | DECIMAL(5,1) | X'40DC28F5C28F5C28' | X'00859C' | Input = .85999999999999999E+0 |
FLOAT(53) | DECIMAL(7,4) | X'4239C65B1343EC2E' | X'0577748C' | Input = .57774781421740208E+2 ROUND is specified |
1 For more examples of INTEGER to CHAR conversions, see 'Converting to character data' on page 372.
The following is the sample of date, time, and timestamp data conversions:
Data type input | Data type column | Data input | Data column | Comments |
|---|---|---|---|---|
No data type | DATE | '01/31/1990' | X'19900131' | Input data type defaults to DATE EXTERNAL |
DATE | DATE | X'19900131' | X'19900131' |
|
DATE EXTERNAL | DATE | '1990-2-29b' | Conversion error | Day out of range |
DATE-2 EXTERNAL CENTURY (1950-2049) | DATE | '900131' | X'19900131' | Year '90' becomes 1990 with CENTURY specification |
DATE-D1 EXTERNAL | DATE | '01/31/90' | X'19900131' |
|
DATE-D1 EXTERNAL CENTURY (1950-2049) | DATE | '01/31/00' | X'20000131' | Year '00' becomes 2000 with CENTURY specification |
DATE-P1E EXTERNAL | DATE | X'002291990C' | Conversion error | Day out of range Input value = 2/29/1990 |
TIME | TIME | X'123000' | '12:30 PMbb’ |
|
TIME-I1 EXTERNAL | TIME | X'0001E078' | X'123000' |
|
TIMESTAMP EXTERNAL(19) | TIMESTAMP | '1990-1-31-12.30.00b' | X'19900131123000000000' |
|
TIMESTAMP EXTERNAL(26) | TIMESTAMP | '1990-01-31-12.30.00.000000' | X'19900131123000000000' |
|
TIMESTAMP-3 EXTERNAL | TIMESTAMP | X'A197DC8F1C200000' | X'19900131123000000000' | Input is 8-byte store clock value Result = 1990-01-31-12.30.00 |
TIMESTAMP | CHAR(19) | X'19900131123000000000' | '1990-01-31-12.30.00' |
|
Related topic