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	Character string data	`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	Binary data	`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"

To Page Top