Hitachi

Hitachi Advanced Database SQL Reference

6.2.2 Data types that can be converted, assigned, and compared

Organization of this subsection

(1) Data types that can be compared

The following table shows the combinations of data types that can be compared.

Table 6‒8: Combinations of data types that can be compared

Data type

Data type of comparison target

Numeric data

Character string data

Datetime data

Binary data

Logical data

UUID data

DATE

TIME

TIMESTAMP

Without time zone

With time zone

Numeric data

Y

N

N

N

N

N

N

N

N

Character string data

N

Y

Y

Y

Y

Y

N

N

N

Datetime data

DATE

N

Y

Y

N

Y

N

N

N

N

TIME

N

Y

N

N

TIMESTAMP (without time zone)

Y

N

Y

N

TIMESTAMP (with time zone)

N

N

N

Y

Binary data

N

N

N

N

N

N

Y

N

N

Logical data

N

N

N

N

N

N

N

Y

N

UUID data

N

N

N

N

N

N

N

N

Y
Legend:

Y: Can be compared.

N: Cannot be compared.

■ Comparing character string data

  • If the lengths of the character string data being compared are different, spaces are added to the end of the shorter data string to make the lengths the same, and then the comparison is performed.

  • Even when comparing VARCHAR types, the comparison is performed after the spaces are added.

  • Even when comparing STRING types, the comparison is performed after the half-width spaces are added.

  • Even when comparing VARCHAR types with STRING types, the comparison is performed after the half-width spaces are added.

■ Comparing numeric data

If the data types of the data being compared are different, the comparison is performed using the data type that has the larger range. The range sizes are ordered as follows:

DOUBLE PRECISION, FLOAT>REAL>DECIMAL, NUMERIC>BIGINT>INTEGER>SMALLINT

However, if the integer data type format is in a legacy format, the range sizes are as follows

DOUBLE PRECISION, FLOAT>REAL>DECIMAL, NUMERIC>INTEGER, BIGINT>SMALLINT
■ Comparing datetime data to character string data

Datetime data can be compared to character string data only when the character string data is a literal written in the corresponding predefined input representation. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

  • Date data can be compared to character strings in the predefined input representation for dates. The predefined input representation for dates is converted from character string data to date data. The comparison is then performed between the date data items.

  • Date data can be compared to character strings in the predefined input representation for time stamps. The date data is supplemented with a time of 0 hours, 0 minutes, and 0 seconds, and is converted to time stamp data. In addition, the predefined input representation for time stamps is converted from character string data to time stamp data. The comparison is then performed between the time stamp data items.

  • Time data can be compared to character strings in the predefined input representation for times. The predefined input representation for times is converted from character string data to time data. The comparison is then performed between the time data items.

  • Time stamp data without time zone can be compared to character strings in the predefined input representation for time stamps without time zone. The predefined input representation for time stamps without time zone is converted from character string data to time stamp data without time zone. The comparison is then performed between the time stamp data without time zone items.

  • Time stamp data with time zone can be compared to character strings in the predefined input representation for time stamps with time zone. The predefined input representation for time stamps with time zone is converted from character string data to time stamp data with time zone. The comparison is then performed between the time stamp data with time zone items.

  • Time stamp data without time zone can be compared to character strings in the predefined input representation for dates. The predefined input representation for dates is supplemented with a time of 0 hours, 0 minutes, and 0 seconds, and is converted to time stamp data without time zone. The comparison is then performed between the time stamp data without time zone items.

However, if the datetime data is located in the selection expression of a subquery, it cannot be compared to the corresponding value expression.

■ Comparing datetime data

  • When date data and time stamp data without time zone are compared, the date data is converted to time stamp data without time zone. In this case, a time of 0 hours, 0 minutes, and 0 seconds is supplemented.

  • When the number of digits in the fractional seconds are different, the lower-precision fractional seconds are padded with zeros until they align with the higher-precision data.

■ Comparing binary data

  • When the data to be compared have the same length, they are considered equal when all the byte values match.

    Example:

    [Figure]

    In the above case, binary data X = binary data Y.

  • When the data to be compared have different lengths, they are considered equal when the following two conditions are met:

    • All the byte values match when compared from the first byte through the end of the shorter data

    • The byte values in the longer portion are all X'00'

    Example:

    [Figure]

    In the above case, binary data X = binary data Y.

  • The data are compared in order starting from the first byte. When the byte values differ, the magnitudes of the first bytes that are different are compared, and this is used to determine which is greater.

    Example:

    [Figure]

    In the above case, binary data X < binary data Y.

  • When the data to be compared have different lengths, and the byte values match from the first byte through the end of the shorter data, which value is greater is determined as follows.

    Let X be the shorter data and Y be the longer data. If there are one or more byte values other than X'00' in the longer portion of Y, then X < Y.

    Example:

    [Figure]

    In the above case, binary data X < binary data Y.

■ Comparing logical data

  • If the data to be compared includes unknown, it is regarded as a null value. For comparison of null values, see 6.7 Null value.

  • If the logical values of the data to be compared are the same, the data is regarded as equal.

  • If the logical values of the data to be compared are different, true is regarded as greater than false.

■ Comparing UUID data

  • If all byte values of the data to be compared match, the data is regarded as equal.

  • The data is compared sequentially from the first byte of each data item. When a different byte value appears, the magnitude of the data to be compared is determined by that byte value.

To Page Top

(2) Storage assignments between data types

The table below lists the combinations of data types that can be specified as an insertion value in an INSERT statement or an update value in an UPDATE statement. However, if you use a dynamic parameter to perform the storage assignment, align data types of the assignment source and assignment target. For details about dynamic parameters, see 6.6 Variables (dynamic parameters).

Table 6‒9: Storage assignment relationships between combinations of data types

Data type of assignment source

Data type of assignment target

Numeric data

Character string data

Datetime data

Binary data

Logical data

UUID data

Row data

DATE

TIME

TIMESTAMP

Without time zone

With time zone

Numeric data

Y

N

N

N

N

N

N

N

N

N

Character string data

N

Y

Y

Y

Y

Y

N

N

N

Y#

Datetime data

DATE

N

N

Y

N

Y

N

N

N

N

N

TIME

N

Y

N

N

TIMESTAMP (without time zone)

Y

N

Y

N

TIMESTAMP (with time zone)

N

N

N

Y

Binary data

N

N

N

N

N

N

Y

N

N

N

Logical data

N

N

N

N

N

N

N

Y

N

N

UUID data

N

N

N

N

N

N

N

N

Y

N
Legend:

Y: Storage assignment can be performed.

N: Storage assignment cannot be performed.

#

A CHAR type can be assigned to row data (ROW).

■ Storage assignment of character string data

  • If the data length of the assignment source is longer than the data length of the assignment target, the assignment cannot be performed.

  • If the assignment target is CHAR type, and the data length of the assignment source is shorter than the data length of the assignment target, it is stored with spaces added at the end.

■ Storage assignment of numeric data

  • If the assignment source exceeds the range of values that can be handled by the assignment target, the assignment cannot be performed.

  • If the assignment target is INTEGER, BIGINT, or SMALLINT, and the assignment source is DECIMAL, NUMERIC, DOUBLE PRECISION, FLOAT, or REAL, the fractional (decimal) part is truncated.

  • If the assignment source and assignment target are both DECIMAL or NUMERIC, any digits of the assignment source that are beyond the scaling of the assignment target are truncated. If the assignment source scaling is smaller than the assignment target scaling, it is stored with zeros added to the fractional part.

  • If the assignment source is DOUBLE PRECISION, FLOAT, or REAL and assignment target is DECIMAL or NUMERIC, any digits of the assignment source that are beyond the scaling of the assignment target are rounded off (to the nearest even number). If the assignment source scaling is smaller than the assignment target scaling, it is stored with zeros added to the end of the fractional part.

■ Storage assignment of character string data to datetime data

Changing the storage assignment from character string data to datetime data is possible only when the character string data is a literal written in the corresponding predefined input representation. For information about predefined input representations, see 6.3.3 Predefined character-string representations.

However, changing the storage assignment is not possible when the datetime data is a column that is to be inserted by an INSERT statement and the character string is specified as the selection expression of a query specification, even when the character string is a literal written in the corresponding predefined input representation.

  • The storage assignment of character strings in the predefined input representation for dates to date data is possible. The character string data in the predefined input representation for dates is converted to date data, and is stored and assigned as date data.

  • The storage assignment of character strings in the predefined input representation for time stamps without time zone to date data is possible. In this case, the character string data in the predefined input representation for time stamps without time zone is converted to time stamp data without time zone. After that, only the date portion of the time stamp data without time zone is stored and assigned.

  • The storage assignment of character strings in the predefined input representation for times to time data is possible. The character string data in the predefined character-string representation for times is converted to time data, and is stored and assigned as time data.

  • The storage assignment of character strings in the predefined input representation for time stamps without time zone to time stamp data without time zone is possible. The character string data in the predefined input representation for time stamps without time zone is converted to time stamp data without time zone. After that, it is stored and assigned as time stamp data without time zone.

  • The storage assignment of character strings in the predefined input representation for time stamps with time zone to time stamp data with time zone is possible. The character string data in the predefined input representation for time stamps with time zone is converted to time stamp data with time zone. After that, it is stored and assigned as time stamp data with time zone.

  • The storage assignment of character strings in the predefined input representation for dates to time stamp data without time zone is possible. In this case, the predefined character-string representation for dates is supplemented with a time of 0 hours, 0 minutes, and 0 seconds and is converted to time stamp data without time zone. After that, it is stored and assigned as time stamp data without time zone.

■ Storage assignment of datetime data

  • When the storage assignment of date data to time stamp data without time zone is performed, the date data is supplemented with a time of 0 hours, 0 minutes, and 0 seconds. After that, it is converted to time stamp data without time zone and storage assignment is performed.

  • When the storage assignment of time stamp data without time zone to date data is performed, only the date portion of the time stamp data without time zone is assigned.

  • If the number of digits in the fractional seconds of the assignment source is greater than the number of digits in the fractional seconds of the assignment target, the portion that cannot be assigned is truncated.

  • If the number of digits in the fractional seconds of the assignment source is less than the number of digits in the fractional seconds of the assignment target, storage assignment is performed by padding the insufficient portion with 0.

■ Storage assignment of binary data

  • If the data length of the source is greater than the data length of the target, the assignment cannot be performed.

  • If the target type is BINARY and the data length of the source is less than the data length of the target, the data is padded with X'00' at the end before it is stored.

■ Storage assignment of logical data

The source data is stored in the target data as is.

■ Storage assignment of UUID data

The source data is stored in the target data as is.

■ Storage assignment of row data

Match the data length of the assignment source to the assumed row data length of the assignment target (the row length of the table being updated or inserted into).

To Page Top

(3) Search assignments of data types

If you receive retrieval results, be sure to align the data types of the assignment source and assignment target.

If ROW (row data) is specified in the selection expression, the data type of the assignment target for receiving the search result can be a CHAR type variable.

To Page Top

(4) Storage assignment to a table function derived table (in the case of the ADB_CSVREAD function)

This subsection describes the rules for how field data in a CSV file is assigned to columns in a table function derived table. It also gives the rules for the description format of the field data.

Note that the table function derived table here means a table function derived table derived by means of the ADB_CSVREAD function.

Note

A table function derived table is a collection of data in table format derived by means of the ADB_AUDITREAD function or the ADB_CSVREAD function. For details about the ADB_AUDITREAD function, see 7.15.2 ADB_AUDITREAD function. For details about the ADB_CSVREAD function, see 7.15.3 ADB_CSVREAD function.

The description format of the field data must be compatible with the data type of the column in the table function derived table. The following table shows the relationship between the data type of the column in the table function derived table and the description format of the field data.

Table 6‒10: Relationship between the data type of the column in the table function derived table and the description format of the field data

No.

Data type of the column in the table function derived table

Description format of the field data

Format

Examples

Notes

Examples of storage assignment of the null value

1

Numeric data

BIGINT
[{+|-}]a...a

+, -: Sign

a...a: Numeric value (a is 0 to 9)

  • 100

  • -123

  • 000

  • 0657

  • The sign and numeric value together cannot exceed 20 characters.

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

  • …,*,…

  • ...,"*",...

  • ...,,...

  • ...,"",...

  • ...,"""",...

However, the examples using enclosing characters are invalid if the enclosing character specification option is set to NONE.

2

INTEGER#9

  • 100

  • -0123

  • 0

  • +0657

  • The sign and numeric value together cannot exceed 11 characters.

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

3

SMALLINT#10

  • The sign and numeric value together cannot exceed 6 characters.

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

4

DECIMAL

[{+|-}]

{a...a[.[b...b]]|.b...b}

+, -: Sign

a...a: Integer part (a is 0 to 9)

b...b: Fractional part (b is 0 to 9)#2

  • 100

  • -123.00

  • Δ.00

  • 012.

  • -1.56

  • +.560

  • The integer and fractional parts together cannot exceed 38 characters (or 39 characters if the integer part (0) is omitted and the precision and scaling match the column where the value is to be stored).

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

5

NUMERIC

6

DOUBLE PRECISION

[{+|-}]

{a...a[.[b...b]]|. b...b}[{E|e}[[{+|-}]c...c]]

+, -: Sign

a...a: Integer part of the mantissa (a is 0 to 9)

b...b: Fractional part of the mantissa (b is 0 to 9)

c...c: Exponent (c is 0 to 9)#3

E, e: Floating point numeric literal (literal identifying the exponent in a floating-point number)

  • 100

  • -Δ 123

  • 0.Δ

  • -1.5600

  • .56

  • -02.4e+9

  • 000e

  • 2.4E+009

  • The data cannot exceed 509 characters.#4

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

7

FLOAT

8

REAL

9

Character string data

CHARACTER
a...a

a...a: Data consisting of one or more characters

  • abcdef [Figure]

  • ABCDEF

  • Δ

  • The number of characters cannot exceed the defined length of the column where the value is to be stored.

  • The trailing half-width spaces in the examples can be omitted.#5

  • …,,…

  • ...,"",...

The character string data cannot include single-byte spaces and tabs.

The examples using enclosing characters are invalid if the enclosing character specification option is set to NONE.

10

VARCHAR
a...a

a...a: Data consisting of one or more characters

  • abcdef [Figure]

  • ABCDEF

  • [Figure]

  • The number of characters cannot exceed the defined length of the column where the value is to be stored.

  • ...,,...

Enclosing characters cannot be specified. The character string data cannot include single-byte spaces and tabs.

■ To specify data of length 0

  • ...,"",...

However, data of length 0 cannot be specified if the enclosing character specification option is set to NONE.

11

Datetime data

DATE

{YYYY-MM-DD

|YYYY/MM/DD}

YYYY: Year (0001 to 9999)

MM: Month (01 to 12)

DD: Day (01 to last day of month)

  • 2013-06-10

  • 2013/06/10

  • Regardless of the format, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

12

TIME
hh:mm:ss[.[nn...n]]

hh: Hour (00 to 23)

mm: Minutes (00 to 59)

ss: Seconds (00 to 59)

nn...n: Fractional seconds (n is 0 to 9)

  • 11:03:58

  • 11:03:58.

  • 11:03:58 Δ.1234

  • The fractional seconds (nn...n) cannot exceed 12 characters.#6

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

13

TIMESTAMP WITHOUT TIME ZONE

{YYYY-MM-DD

|YYYY/MM/DD}

Δhh:mm:ss[.[nn...n]]

YYYY: Year (0001 to 9999)

MM: Month (01 to 12)

DD: Day (01 to last day of month)

hh: Hour (00 to 23)

mm: Minutes (00 to 59)

ss: Seconds (00 to 59)

nn...n: Fractional seconds (n is 0 to 9)

  • 2013-06-10 Δ 11:03:58

  • 2013-06-10 Δ 11:03:58 Δ .1234

  • The fractional seconds (nn...n) cannot exceed 12 characters.#6

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

14

TIMESTAMP WITH TIME ZONE

{YYYY-MM-DD

|YYYY/MM/DD}

Δhh:mm:ss[.[nn...n]][{{+|-}hh:mm|Z|z}]

YYYY: Year (0001 to 9999)

MM: Month (01 to 12)

DD: Day (01 to last day of month)

hh: Hour (00 to 23)

mm: Minutes (00 to 59)

ss: Seconds (00 to 59)

nn...n: Fractional seconds (n is 0 to 9)

Z or z: Symbol indicating UTC

  • 2025-06-10Δ11:03:58+09:00

  • 2025-06-10Δ11:03:58Δ.1234-04:00

  • 2025-12-30Δ12:34:56Z

Same as No. 1.

15

Binary data

BINARY

Hexadecimal string

a...a

a: 0 to 9, A to F, or a to f

  • 12340000

  • 90Δ AB

  • 90ab Δ CDEF

  • The number of characters must be a multiple of 2, up to 2 times the defined length of the column where the value is to be stored.#7

  • Trailing 00s are assumed and can be omitted.#8

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

16

Binary string

a...a

a: 0 or 1

  • 01010101

  • 0101 Δ 0101

  • The number of characters must be a multiple of 8, up to 8 times the defined length of the column where the value is to be stored.#7

  • Trailing 00000000s are assumed and can be omitted.#8

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

17

VARBINARY

Hexadecimal string

a...a

a: 0 to 9, A to F, or a to f

  • 12340000

  • 90Δ AB

  • 90ab Δ CDEF

  • The number of characters must be a multiple of 2, up to 2 times the defined length of the column where the value is to be stored.#7

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

  • …,*,…

  • ...,"*",...

  • ...,,...

However, the examples using enclosing characters are invalid if the enclosing character specification option is set to NONE.

■ To specify data of length 0

  • ...,"",...

  • ...,"""",...

However, data of length 0 cannot be specified if the enclosing character specification option is set to NONE.

18

Binary string

a...a

a: 0 or 1

  • 01010101

  • 0101 Δ 0101

  • The number of characters must be a multiple of 8, up to 8 times the defined length of the column where the value is to be stored.#7

  • Regardless of the format and length restrictions, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 16.

19

Logical data

BOOLEAN
{TRUE|FALSE}

TRUE: True

FALSE: False

Mixing uppercase and lowercase letters is acceptable.

  • TRUE

  • FALSE

  • True

  • False

  • true

  • false

  • Regardless of the format, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.

20

UUID data

UUID
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa

a: 0 to 9, A to F, or a to f

  • 12345678-1234-1234-1234-123456789AbC

  • Δ12345678-1234-1234-1234-123456Δ789ABC

  • Regardless of the format, leading and trailing half-width spaces and tabs are permitted around all characters.#1

Same as No. 1.
Legend:

Δ: One or more single-byte spaces or tabs

,: Delimiting character

": Enclosing character

#1

Any leading or trailing single-byte space (0x20) or tab (0x09) characters are removed.

Example: Δ1Δ23[Figure]4[Figure] → 1234

If the removal of white space leaves no data, the result is treated as a null value.

#2

Fractional digits beyond the scaling defined for the column where the value is to be stored are truncated.

#3

If the exponent is omitted, an exponent of +0 is assumed.

#4

Depending on the specified value, loss of precision might occur.

#5

If the input data is less than the defined length, the remaining portion is filled with single-byte spaces.

#6

If the number of digits in the fractional seconds (nn...n) is less than the fractional seconds precision of the data type in the table, the stored value is filled with zeros on the right.

If the number of digits in the fractional seconds (nn...n) exceeds the fractional seconds precision of the data type in the table, the input data is truncated.

#7

An error results if the number of characters in the hexadecimal string is not a multiple of 2.

An error results if the number of characters in the binary string is not a multiple of 8.

#8

When the input data is less than the defined length, the remaining portion is filled with 0x00.

#9

If the integer data type format is a legacy format, the field data description format is the same as that of BIGINT in No.1.

#10

If the integer data type format is a legacy format, the field data description format is the same as that of INTEGER in No.2.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.