Hitachi

Hitachi Advanced Database Command Reference


14.5.3 Output format for field data

The table below describes the output format for field data by data type.

In the case of the null value, no data is output regardless of the data type. The plus sign (+) is not output for numeric data.

Table 14‒5: Output format for field data

No.

Data type

Output format for field data

1

Numeric data

INTEGER

Output format
  • An integer is output.

  • The plus sign (+) is not output for a positive number. The minus sign (-) is output for a negative number.

  • No data is output for the null value.

Output examples
  • +1234 1234

  • 0 0

  • -1234 -1234

2

SMALLINT

3

DECIMAL

Output format
  • Data is output in the format integer[.decimal].

    Integer part: As many digits as there are in the data are output. However, if the scaling and precision are the same, a single 0 is output.

    Decimal places: If the scaling is 0, the decimal point and the digits following it are not output. If the scaling is 1, as many digits as there are in the data are output to the fraction part. However, if the fraction part consists of only zeros, a single 0 is output.

  • The plus sign (+) is not output for a positive number. The minus sign (-) is output for a negative number.

  • No data is output for the null value.

Output examples
  • +123.45 123.45

  • -.123 -0.123

  • +123. 123

  • +123.45000 123.45

  • +123.00000 123.0

4

DOUBLE PRECISION

Output format
  • Data is output in the format mantissaEexponent.

    Mantissa part: As many digits as there are in the data are output for both the mantissa and the exponent. However, if the fraction part consists of only zeros, a single 0 is output.

    Exponent part: As many digits as there are in the data are output.

  • The plus sign (+) is not output for a positive number. The minus sign (-) is output for a negative number. The plus sign (+) is not output for the exponent.

  • No data is output for the null value.

Output examples
  • +1.1234567890000000E-09 1.123456789E-9

  • +0.0000000000000000E+00 0.0E0

  • -0.1230000000000000E-123 -0.123E-123

5

Character string data

CHAR

Output format
  • A character string enclosed in the enclosing character is output.

  • Any spaces to the right of the data are deleted. If the data consists of only spaces, a single space is output.

  • No data is output for the null value.

Output example
  • ABCD"ABCD"

This example uses the double quotation mark (") as the enclosing character.

6

VARCHAR

Output format
  • A character string enclosed in the enclosing character is output.

  • Data with the length of real data is output.

  • For data with a length of zero bytes, only two instances of the enclosing character are output.

  • No data is output for the null value.

Output examples
  • ABCD"ABCD"

  • (data-with-a-length-of-zero-bytes)""

This example uses the double quotation mark (") as the enclosing character.

7

Datetime data

DATE

Output format
  • A date enclosed in the enclosing character is output.

  • The date part is output in the format YYYY-MM-DD.

    The hyphen delimits the year (YYYY), month (MM), and date (DD).

  • No data is output for the null value.

Output example
  • 2013-06-10"2013-06-10"

This example uses the double quotation mark (") as the enclosing character.

8

TIME

Output format
  • A date and time enclosed between the enclosing character is output.

  • The time is output in the format hh:mm:ss.nn....n.

    The hour (hh), minute (mm), and second (ss) are delimited with the colon (:). If the fractional seconds precision is 1 or greater, the fraction of a second is displayed as .nn....n.

  • Any trailing zeros are deleted from the fraction of a second. If the fraction of a second consists of all zeros, only one zero is output.

  • No data is output for the null value.

Output examples
  • 11:03:58"11:03:58"

  • 15:36:08.580"15:36:08.58"

  • 08:03:44.000000"08:03:44.0"

This example uses the double quotation mark (") as the enclosing character.

9

TIMESTAMP

Output format
  • A date and time enclosed in the enclosing character is output.

  • A date and time are output in the format YYYY-MM-DDΔhh:mm:ss.nn....n.

    The hyphen delimits the year (YYYY), month (MM), and date (DD), followed by a single-byte space (Δ), and then the hour (hh), minute (mm), and second (ss) follow, delimited with the colon (:). If the fractional seconds precision is 1 or greater, the fraction of a second is displayed as .nn....n.

  • Any trailing zeros are deleted from the fraction of a second. If the fraction of a second consists of all zeros, only one zero is output.

  • No data is output for the null value.

Output examples
  • 2013-06-10 11:03:58"2013-06-10 11:03:58"

  • 2014-07-15 10:11:28.330"2014-07-15 10:11:28.33"

  • 2014-07-08 07:12:44.000"2014-07-08 07:12:44.0"

    This example uses the double quotation mark (") as the enclosing character.

10

Binary data

BINARY

Output format
  • Binary data in hexadecimal format that is enclosed in the enclosing character is output.

  • No data is output for the null value.

Output example
  • 12345678"12345678"

  • 90ABCDEF"90ABCDEF"

This example uses the double quotation mark (") as the enclosing character.

11

VARBINARY

Output format
  • Binary data in hexadecimal format that is enclosed in the enclosing character is output.

  • For data with a length of zero bytes, only two instances of the enclosing character are output.

  • No data is output for the null value.

Output example
  • 12345678"12345678"

  • 90ABCDEF"90ABCDEF"

  • (data-with-a-length-of-zero-bytes) → ""

This example uses the double quotation mark (") as the enclosing character.

Note
  • If you want to output datetime data in the format YYYY/MM/DD, specify a CONVERT scalar function in the selection expression in the SELECT statement that is specified in the SQL statement file. For details about the CONVERT scalar function, see Scalar Functions in the manual HADB SQL Reference.

    Example 1: Exporting date data of the DATE type in column C1 in the format YYYY/MM/DD

    SELECT CONVERT("C1",CHAR(10),'YYYY/MM/DD'), "C2" FROM "T1"

    Example 2: Exporting time stamp data of the TIMESTAMP type in column C1 in the format YYYY/MM/DDhh:mm:ss

    SELECT CONVERT("C1",CHAR(19),'YYYY/MM/DD HH:MI:SS'), "C2" FROM "T1"

  • If you want to output binary data in binary format enclosed between enclosing characters, specify a BIN scalar function in the selection expression in the SELECT statement that is specified in the SQL statement file. For details about the BIN scalar function, see Scalar Functions in the manual HADB SQL Reference.

    Example: If you want to output the binary data in column C1 in binary format

    SELECT BIN("C1") FROM "T1"