8.12.5 CONVERT
Converts the data type of the data.
You can also specify a datetime format or number format to control the conversion.
-
If you specify a datetime format
-
When converting datetime data to character string data, you can specify the output format of the character string data after conversion.
-
When converting character string data to datetime data, you can specify the input format of the character string data before conversion.
-
-
If you specify a number format
-
When converting numeric data to character string data, you can specify the output format of the character string data after conversion.
-
When converting character string data to numeric data, you can specify the input format of the character string data before conversion.
-
The following examples illustrate the result of executing the scalar function CONVERT.
- Example 1::
-
-
Convert the DECIMAL type data -12.37 to INTEGER type.
CONVERT(-12.37,INTEGER) → -12
-
- Example 2: Specify a datetime format
-
-
Convert the TIMESTAMP type data TIMESTAMP'2013-07-30 11:03:58' to CHAR(10) data.
CONVERT(TIMESTAMP'2013-07-30 11:03:58',CHAR(10),'YYYY/MM/DD') → '2013/07/30'
-
Convert the CHAR type data 07/15/2013 12:34:56, which represents a datetime, to TIMESTAMP type.
CONVERT('07/15/2013 12:34:56',TIMESTAMP,'MM/DD/YYYY HH:MI:SS') → TIMESTAMP'2013-07-15 12:34:56'
-
- Example 3: Specify a number format
-
-
Convert INTEGER type data to CHAR(7) data, and make it start with $ and have a comma between every 3 digits.
CONVERT(1000,CHAR(7),'$9,999') → 'Δ$1,000'
CONVERT(-1000,CHAR(7),'$9,999') → '-$1,000'
Δ represents a single-byte space character.
-
Convert INTEGER type data that starts with $ and has a comma between every 3 digits to CHAR type data.
CONVERT('$1,000,000',INTEGER,'$9,999,999') → 1000000
CONVERT('-$1,000',INTEGER,'$9,999,999') → -1000
-
- Organization of this subsection
(1) Specification format
scalar-function-CONVERT ::= CONVERT(data-to-convert,post-conversion-data-type[,format-specification]) data-to-convert ::= {value-expression|NULL} post-conversion-data-type ::= data-type format-specification ::= {datetime-format|number-format} datetime-format ::= literal number-format ::= literal
(2) Explanation of specification format
- data-to-convert:
-
Specifies the data whose data type is to be converted.
Specify the data to be converted in the form of a value expression. Alternatively, specify NULL. For details about value expressions, see 7.20 Value expression.
- post-conversion-data-type:
-
Specifies the data type after conversion. The following are examples:
-
INTEGER
Convert to INTEGER type data.
-
DECIMAL(5,2)
Convert to DECIMAL type data with a precision of 5 and a scaling of 2.
-
CHAR(8)
Convert to CHAR type data with a data length of 8 bytes.
-
TIMESTAMP(3)
Convert to TIMESTAMP type data with a fractional seconds precision of 3.
For the specification formats of each data type, see 6.2.1 List of data types.
Note that you cannot specify a VARCHAR type whose data length exceeds 32,000 bytes for the post-conversion-data type.
-
- format-specification:
-
Specifies a datetime format or a number format.
- datetime-format:
-
Specifies the datetime format in either of the following cases:
-
When converting datetime data to character string data, specifies the output format of the character string data after conversion.
-
When converting character string data to datetime data, specifies the input format of the character string data before conversion.
Specify a character string literal for the datetime format. For details about character string literals, see 6.3 Literals.
The following are examples of datetime formats:
Examples:
'YYYY-MM-DD HH:MI:SS'
'YYYY/MM/DD HH MI SS FF3'
'YYYY.MM.DD-HH:MI:SS.FF6'
'YYYY:MM'
'MM/DD-HH'
Items such as YYYY, MM, and DD in the examples above are called datetime format elements. For details about the elements that can be specified in the datetime format, see (3) Datetime format elements and rules.
The following examples illustrate the result of executing the scalar function CONVERT when a datetime format is specified.
-
Examples of converting datetime data to character string data
Example of CONVERT specification
Execution result
CONVERT(DATE'2013-01-01',VARCHAR(20),'YYYY/MM/DD')
'2013/01/01'
CONVERT(DATE'2013-01-01',VARCHAR(20),'CC"st century"')
'21st century'
CONVERT(DATE'2013-01-01',VARCHAR(20),'EYYN/Q"Q"')
'H25/1Q'
CONVERT(DATE'2013-01-01',VARCHAR(20),'YY-WW')
'13-01'
CONVERT(DATE'2013-01-01',VARCHAR(20),'DD-Mon-YY')
'01-Jan-13'
CONVERT(DATE'2013-01-01',VARCHAR(20),'YYYY/MM/DD"("DY")"')
'2013/01/01(TUE)'
CONVERT(TIME'09:15:20.12',VARCHAR(20),'FMHH:MI:SS.FF6')
'9:15:20.120000'
-
Examples of converting character string data to datetime data
Example of CONVERT specification
Execution result
CONVERT('01/02/2012 12:34:56',TIMESTAMP,'mm/dd/yyyy hh:mi:ss')
TIMESTAMP'2012-01-02 12:34:56'
CONVERT('2511 10235',TIMESTAMP,
'fmeeyyn""mm""dd""pmnhh12""mi""ss""')
TIMESTAMP'2013-01-01 10:23:05'
CONVERT('1 2 3 45',TIME(6),'FMHH MI SS FF2')
TIME'01:02:03.450000'
-
- number-format:
-
Specifies the number format in either of the following cases:
-
When converting numeric data to character string data, specifies the output format of the character string data after conversion.
-
When converting character string data to numeric data, specifies the input format of the character string data before conversion.
Specify a character string literal for number format. For details about character string literals, see 6.3 Literals.
The following are examples of number format specifications.
Examples:
'$9,999,999'
'00,000.00'
Items such as $, 0, 9, . (period) and , (the three-digit comma separator) in the examples above are called the elements of the number format. For details about the elements that can be specified in a number format, see (4) Number format elements and rules.
The following examples illustrate the result of executing the scalar function CONVERT when a number format is specified.
-
Examples of converting numeric data to character string data
Example of CONVERT specification
Execution result
CONVERT(1234567,CHAR(10),'9,999,999')
'Δ1,234,567'
CONVERT(1234,CHAR(10),'0,000,000')
'Δ0,001,234'
CONVERT(-1000,CHAR(7),'$9,999')
'-$1,000'
CONVERT(1000,VARCHAR(12),'LJ9,999"dollars"')
'1,000dollars'
Δ represents a single-byte space character.
-
Examples of converting character string data to numeric data
Example of CONVERT specification
Execution result
CONVERT('1,234,567',INTEGER,'9,999,999')
1234567
CONVERT('12',INTEGER,'9,999,999')
12
CONVERT('$1,000,000',INTEGER,'$9,999,999')
1000000
CONVERT('1,000dollars',INTEGER,'9,999"dollars"')
1000
CONVERT('+1.23E+10floating-point-character-string',DOUBLE PRECISION,'9.99EEEE"floating-point-character-string"')
1.2300000000000000E10
-
(3) Datetime format elements and rules
(a) Datetime format elements
The table below shows the elements that can be specified in the datetime format.
No. |
Meaning of datetime format |
Element that can be specified in the datetime format |
Description |
|
---|---|---|---|---|
1 |
Century |
CC |
Represents the century. The range of values is 00 to 99. Note that 00 represents the 100th century (the years 9901 to 9999 CE). |
|
2 |
Year |
YYYY |
Represents the four-digit Western calendar year (CE). Values in the range 0001 to 9999 can be used. Note that when converting character string data to datetime data, you cannot specify an era name if YYYY is specified. |
|
3 |
YY |
Represents the lower two digits of the year. The range of values is 00 to 99. |
||
4 |
Era name |
E |
Represents the abbreviated form of the era name in Japanese.
|
When converting character string data to datetime data, an era name must be specified together with a Japanese calendar year. |
5 |
EE |
Represents the era name in Japanese.
|
||
6 |
Japanese calendar year |
YYYYN |
Represents a four-digit Japanese calendar year. The following shows the range of values that can be specified for each era:
|
When converting character string data to datetime data, a Japanese calendar year must be specified together with an era name. |
7 |
YYN |
Represents a two-digit Japanese calendar year. Values in the range 00 to 99 can be used. Note that if a year of three or more digits occurs when converting datetime data to character string data, only the lower two digits are converted. |
||
8 |
Quarter |
Q |
Represents the quarter. The range of values is 1 to 4.
|
|
9 |
Month |
MM |
Represents the month. Values in the range 01 to 12 can be used. |
|
10 |
MON |
Represents the abbreviated form of the name of the month in English.
|
||
11 |
MONTH |
Represents the name of the month in English.
Δ represents a single-byte space. |
||
12 |
Week |
W |
Represents the week within the month. The range of values is 1 to 5.
Note that the range of values will be 1 to 4 in February (except for leap years). |
|
13 |
WW |
Represents the week within the year. The range of values is 01 to 53. For example, 01 represents January 1 to January 7, 02 represents January 8 to January 14, and so on. |
||
14 |
Day |
DD |
Represents the ordinal date from the beginning of the month. The range of values is from 01 until the last day of the relevant month. |
|
15 |
DDD |
Represents the ordinal date from the beginning of the year. Values in the range 001 to 365 (001 to 366 in leap years) can be used. For example, 001 represents January 1, and 002 represents January 2. 032 represents February 1. |
||
16 |
Day of week |
D |
Represents the day of the week expressed as a number. Values in the range 1 to 7 can be used.
|
|
17 |
DAY |
Represents the day of the week in English.
Δ represents a single-byte space. |
||
18 |
DY |
Represents the abbreviated form of the day of the week in English.
|
||
19 |
DAYN |
Represents the day of the week in Japanese. Possible values are '': Sunday, '': Monday, '': Tuesday, '': Wednesday, '': Thursday, '': Friday, '': Saturday. |
||
20 |
DYN |
Represents the abbreviated form of the day of the week in Japanese. Possible values are '': Sun, '': Mon, '': Tues, '': Weds, '': Thur, '': Fri, '': Sat. |
||
21 |
Hour |
HH |
Represents the hour. Values in the range 00 to 23 can be used. When converting character string data to datetime data, HH and HH24 cannot be specified with an AM/PM designation. |
|
22 |
HH24 |
|||
23 |
HH12 |
Represents the hour. Values in the range 01 to 12 can be used. When converting character string data to datetime data, HH12 must be specified together with an AM/PM designation. |
||
24 |
AM/PM |
AM |
Represents the AM or PM designation in English.#1 |
When converting character string data to datetime data, the AM/PM designation must be specified together with HH12. |
25 |
A.M. |
|||
26 |
PM |
|||
27 |
P.M. |
|||
28 |
AMN |
Represents the AM or PM designation in Japanese.#2 |
||
29 |
PMN |
|||
30 |
Minute |
MI |
Represents the minute. Values in the range 00 to 59 can be used. |
|
31 |
Second |
SS |
Represents the second. Values in the range 00 to 59 can be used. |
|
32 |
SSSSS |
Represents seconds. Values in the range 00000 to 86399 can be used. The value represents the number of seconds that have elapsed since 00:00:00 (midnight). For example, 03600 denotes 1:00:00 AM. |
||
33 |
Fractional seconds |
FF |
Represents the fractional seconds. When converting from character string data to datetime data, this is the number of digits of fractional seconds of post-conversion-data-type. When converting from datetime data to character string data, this is the number of digits of fractional seconds of data-to-convert.
|
|
34 |
FF1 |
Represents 1 digit of fractional seconds (0 to 9). |
||
35 |
FF2 |
Represents 2 digits of fractional seconds (00 to 99). |
||
36 |
FF3 |
Represents 3 digits of fractional seconds (000 to 999). |
||
37 |
FF4 |
Represents 4 digits of fractional seconds (0000 to 9999). |
||
38 |
FF5 |
Represents 5 digits of fractional seconds (00000 to 99999). |
||
39 |
FF6 |
Represents 6 digits of fractional seconds (000000 to 999999). |
||
40 |
FF7 |
Represents 7 digits of fractional seconds (0000000 to 9999999). |
||
41 |
FF8 |
Represents 8 digits of fractional seconds (00000000 to 99999999). |
||
42 |
FF9 |
Represents 9 digits of fractional seconds (000000000 to 999999999). |
||
43 |
FF10 |
Represents 10 digits of fractional seconds (0000000000 to 9999999999). |
||
44 |
FF11 |
Represents 11 digits of fractional seconds (00000000000 to 99999999999). |
||
45 |
FF12 |
Represents 12 digits of fractional seconds (000000000000 to 999999999999). |
||
46 |
Delimiting character |
- (hyphen) |
Characters used as delimiters between elements. Example 'YYYY-MM-DD HH:MI:SS' |
|
47 |
/ (slash) |
|||
48 |
, (comma) |
|||
49 |
. (period) |
|||
50 |
: (colon) |
|||
51 |
; (semicolon) |
|||
52 |
Space |
|||
53 |
Other |
"character-string" |
You can specify an arbitrary string in double quotation marks ("). Example CONVERT(DATE'2013-01-01',VARCHAR(20),'CC""') → '21 ' (: Represents the century.) The underlined portion indicates the relevant section. To specify a double quotation mark within the string itself, specify two consecutive double quotation marks (""). Example: Specify the character string AB"CD "AB""CD" |
|
54 |
FM |
Controls whether to delete the single-byte spaces at the end of the character strings denoted by MONTH and DAY, and whether to suppress zeros in numbers such as YYYY. For details, see (c) How to specify the datetime format element FM. |
- #1
-
-
When converting character string data to datetime data, the conversion result will be the same regardless of whether you specify A.M., AM, P.M., or PM. If the target data uses AM, A.M., PM, or P.M., the conversion result will all be the same regardless of whether you specify AM, A.M., PM, or P.M. in the datetime format element. Uppercase and lowercase letters are treated the same.
-
When converting datetime data to character string data, the conversion result will be the same regardless of whether you specify AM or PM. The conversion result will also be the same regardless of whether you specify A.M. or P.M.. The only difference between using AM vs. A.M., or PM vs. P.M., is whether the periods will appear in the character string after conversion.
-
- #2
-
The execution results will be the same whether you specify AMN or PMN. The corresponding Japanese character strings are for AM and for PM.
- Note
-
When datetime data is converted to character string data, it is converted based on the value of the datetime data to be converted, regardless of the specification of AM, PM, or other elements in the datetime format.
When character string data is converted to datetime data, it follows the specification of AM, PM, or other elements in the character string data to be converted, regardless of the specification of AM, PM, or other elements in the datetime format.
(b) Rules pertaining to datetime format
-
The length of the datetime format cannot exceed 64 bytes.
-
Characters specified in the datetime format that are not enclosed in double quotation marks (") must be single-byte.
-
The letters specified in the datetime format are not case-sensitive. However, the following letters are case-sensitive:
-
The first letter in AM, A.M., PM, and P.M.
-
The first two letters in MON, MONTH, DAY, and DY
-
The letters in character strings that are enclosed in double quotation marks (")
-
-
When converting character string data to datetime data, the following datetime format elements cannot be specified:
-
CC (century)
-
Q (quarter)
-
WW (week within the year)
-
W (week within the month)
-
YY (year expressed in two digits)
-
-
When converting TIME type data to character string data, you cannot specify the following datetime formatting elements:
-
CC (century)
-
YYYY, YY (year)
-
E, EE (era name)
-
YYYYN, YYN (Japanese calendar year)
-
Q (quarter)
-
MM, MON, MONTH (month)
-
W, WW (week)
-
DD, DDD (day)
-
D, DAY, DAYN, DY, DYN (day of week)
-
-
When converting character string data to datetime data, you cannot specify two or more datetime format elements with the same meaning. For example, the following are not allowed:
- Example 1: 'YYYY-MM-DD-YYYY'
-
YYYY cannot be specified twice.
- Example 2: 'YYYY-MM-DD-EYYN'
-
Because YYYY and YYN are datetime format elements with the same meaning, they cannot both be specified.
The datetime format elements with the same meaning are shown in the following table:
Table 8‒49: Datetime format elements with the same meaning No.
Meaning of datetime format
Datetime format elements with the same meaning
1
Year
YYYY
2
YYYYN
3
YYN
4
Era name
E
5
EE
6
Month
MM
7
MON
8
MONTH
9
DDD
10
Day
DD
11
DDD
12
Hour
HH
13
HH24
14
HH12
15
SSSSS
16
AM/PM
AM
17
A.M.
18
PM
19
P.M.
20
AMN
21
PMN
22
Minute
MI
23
SSSSS
24
Second
SS
25
SSSSS
26
Fractional seconds
FF
27
FF1
28
FF2
29
FF3
30
FF4
31
FF5
32
FF6
33
FF7
34
FF8
35
FF9
36
FF10
37
FF11
38
FF12
-
When converting character string data to datetime data, if you specify the day of the week (D, DAY, DY, DAYN, or DYN) and the specified day of the week conflicts with the specified date, it does not result in an error.
-
If you specify AM, A.M., PM, or P.M. in the datetime format when converting datetime data to character string data, if the first letter is uppercase, the entire element is output in uppercase, and if the first letter is lowercase, the entire element is output in lowercase.
-
If an era name is used as a datetime format element, the range of the corresponding Western calendar years will be January 1, 1873 (January 1, Meiji 6) to December 31, 9999 (December 31, Reiwa 7981). The ranges of the corresponding Japanese calendar years are as follows.
-
Meiji: 06/01/ 01 to 45/07/29
-
Taisho: 01/07/30 to 15/12/ 24
-
Showa: 01/12/25 to 64/01/ 07
-
Heisei: 01/01/08 to 31/04/30
-
Reiwa: 01/05/01 to 7981/12/31
However, if you specify Heisei as the era name when converting character string data into datetime data, you can specify December 31, Heisei 8011 or an earlier date.
- Example:
-
CONVERT('05/01/0031/',DATE,'MM/DD/YYYYN/EE') → 2019-05-01
CONVERT('12/31/8011/H',DATE,'MM/DD/YYYYN/E') → 9999-12-31
-
-
The time represented by 0 hours in the HH24 representation is equivalent to the time 12:00 AM in the HH12 representation. The time represented by 12 hours in the HH24 representation is equivalent to the time 12:00 PM in the HH12 representation.
-
Elements are extracted in order from the beginning (left) of the character string specified as the datetime format. In cases of overlapping element names, the longest possible element name is extracted. For example, if 'DDD' is specified, the first element extracted will be DDD, not D or DD.
-
When converting character string data to datetime data, if you specify a character string enclosed in double quotation marks, make sure its case is consistent with the case of the letters in the target data. Note that uppercase and lowercase letters are distinguished inside a character string enclosed in double quotation marks when the character string is output.
-
When converting character string data to datetime data, the conversion of E (Japanese era) is the same regardless of the case of the letters in the data being converted. When converting datetime data to character string data, the character string associated with E is always output in upper case.
(c) How to specify the datetime format element FM
- ■ When converting datetime data to character string data
-
If FM is not specified when MONTH or DAY is specified as an element of the datetime format, the character string after conversion is always nine characters long. If the resulting character string is shorter than nine characters, half-width spaces are added to produce a nine-character string.
Furthermore, zeros in the year, month, and date are not suppressed.
Example without FM:
CONVERT(DATE'2014-01-05',CHAR(17),'YYYY-MONTH-DD') → '2014-JANUARY∆∆-05'
The half-width spaces following JANUARY are not deleted. Two half-width spaces are added to produce a nine-character string. Note that the zero in 05, which is the day number, is not suppressed.
Example with FM:
CONVERT(DATE'2014-01-05',CHAR(14),'FMYYYY-MONTH-DD') → 2014-JANUARY-5
The half-width spaces following JANUARY are deleted. Note that the zero in 05, which is the day number, is suppressed.
Specify the datetime format element FM when you want to suppress zeros and remove single-byte spaces in the post-conversion character string data in this way.
In addition, by specifying FM in the middle of the datetime format, you can control the point at which this takes effect.
Example:
- Explanation
-
-
The half-width spaces in the character string that corresponds to MONTH (in this example, JANUARY∆∆) are not deleted. Note that the zeros in the numbers that correspond to YYYY (in this example, 0123) and DD (in this example, 01) are not suppressed.
-
The character string corresponding to MONTH is JANUARY, with the spaces removed. Furthermore, the zeros are suppressed from the numbers corresponding to YYYY (123) and DD (1).
-
The half-width spaces in the character string that corresponds to MONTH (in this example, JANUARY∆∆) are not deleted. Note that zeros in the numbers that correspond to YYYY (in this example, 0123) and DD (in this example, 01) are not suppressed.
-
- ■ When converting character string data to datetime data
-
-
When the datetime format element MONTH or DAY is specified, spaces are required in the character string data to be converted (for example, JANUARY). If the character string data to be converted does not include the spaces (for example, JANUARY), it can still be converted if the datetime format element FM is specified.
<Example that results in an error>
CONVERT('2014-JANUARY-05',DATE,'YYYY-MONTH-DD') → Error
This results in an error because the two spaces are missing from the end of JANUARY.
<Example that does not result in an error>
CONVERT('2014-JANUARY-05',DATE,'FMYYYY-MONTH-DD') → DATE'2014-01-05'
Because FM is specified, the two spaces are not required at the end of JANUARY.
Note that an error results if you express the month as JANUARY when FM is specified.
- Important
-
When FM is specified, the conversion results might not always come out as intended. For example, if you attempt to convert the character string '2014111' to a DATE type value representing January 11, 2014, you will not obtain the intended result, as illustrated below.
CONVERT('2014111',DATE,'FMYYYYMMDD') → DATE'2014-11-01'
In the above example, the character string is converted to November 1, 2014.
-
When you specify one of the datetime format elements listed in Table 8‒50: Datetime format elements specifying numbers and their maximum number of characters including leading zeros as a numeric character, make sure that the number of numeric characters in the character string data to be converted is equal to the maximum number of characters indicated in Table 8‒50: Datetime format elements specifying numbers and their maximum number of characters including leading zeros. For example, if the element MM is specified, the numeric character representing the months January through September must be expressed as 01 to 09 in the character string data (the leading zero is required). If there is no leading zero in the character string data to be converted, it can still be converted if the datetime format element FM is specified (no error results regardless of whether the leading zero is there).
<Example that results in an error>
CONVERT('2014:1:5',DATE,'YYYY:MM:DD') → Error
<Examples that do not result in an error>
CONVERT('2014:1:5',DATE,'FMYYYY:MM:DD') → DATE'2014-01-05'
CONVERT('2014:01:05',DATE,'FMYYYY:MM:DD') → DATE'2014-01-05'
-
Even when the value of an element listed in Table 8‒50: Datetime format elements specifying numbers and their maximum number of characters including leading zeros that you specify with a numeric character is 0, you must specify at least one character for each element in the data to be converted. For example, when converting 0 hours, 0 minutes, and 0 seconds with the datetime format 'FMHH:MI:SS', the conversion works when the data to be converted is '0:0:0', but generates an error when the data to be converted is '0:0:'.
However, in the case of elements FF and FF1 to FF12, which are unaffected by FM, you can omit the specification of 0. For example, when converting 0 hours, 0 minutes, and 0.000 seconds with the datetime format 'FMHH:MI:SS.FF3', the conversion works even when the data to be converted is '0:0:0.'.
-
By specifying FM in the middle of the datetime format, you can control the point at which this takes effect.
-
When FM is specified, HADB identifies the extent of a number corresponding to an element such as MM and DD based on either the first occurrence of a non-digit character or the point when the maximum number of characters in the specified datetime format has been reached. The following table shows the datetime format elements specifying numbers and their maximum number of characters, including leading zeros.
Table 8‒50: Datetime format elements specifying numbers and their maximum number of characters including leading zeros No.
Datetime format element specifying number
Maximum number of characters including leading zeros
1
YYYY
4
2
YYYYN
4
3
YYN
2
4
MM
2
5
DD
2
6
DDD
3
7
D
1
8
HH
2
9
HH24
2
10
HH12
2
11
MI
2
12
SS
2
13
SSSSS
5
14
FF
Not applicable
15
FF1
16
FF2
17
FF3
18
FF4
19
FF5
20
FF6
21
FF7
22
FF8
23
FF9
24
FF10
25
FF11
26
FF12
-
(4) Number format elements and rules
(a) Specification format for number format elements
This subsection describes the specification format for number format elements. Note that you must close up the spacing for any elements you omit. An error results if elements are specified in the wrong order, or if elements are specified where they are not permitted.
number-format ::= {fixed-point-representation | floating-point-representation | shortest-representation | hexadecimal-representation} fixed-point-representation ::= ["character-string"][[modifier-element][sign-element][B][currency-element] [numeric-element[[{delimiting-character-element | numeric-element}]...numeric-element]][.] [numeric-element]...[sign-element]["character-string"]] floating-point-representation ::= ["character-string"][modifier-element][numeric-element]...[.][numeric-element]... floating-point-element["character-string"] shortest-representation ::= ["character-string"]{TM | TM9 | TME}["character-string"] hexadecimal-representation ::= ["character-string"][modifier-element][0]...hexadecimal-element[hexadecimal-element]...["character-string"] modifier-element ::= {LJ | LS} sign-element ::= {MI | S | PR} currency-element ::= {$ | } numeric-element ::= {0 | 9} delimiting-character-element ::= {, | Δ} floating-point-element ::= {EEEE | eeee} hexadecimal-element ::= {X | x}
- Note:
-
-
The delimiting character element Δ represents a single-byte space character.
-
"character-string" represents an arbitrary character string enclosed in double quotation marks.
-
(b) Number format element
The table below shows the elements that can be specified in the number format.
In the table, Δ represents a single-byte space character.
No. |
Type of element |
Element that can be specified in the number format |
Description |
---|---|---|---|
1 |
Delimiting character element |
, (comma) |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
2 |
Δ (single-byte space) |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
|
3 |
Decimal point character |
. (period) |
Specifies the position of the decimal point using a period. In the number format, the numeric element before the period represents the integer part, and the numeric element after the period represents the decimal part. ■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
4 |
Currency element |
$ |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
5 |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
||
6 |
Numeric element |
0 |
Represents a single numeric digit when converting the digits corresponding to a numeric value in the data to be converted. The following description pertains to the numeric element 0 specified in a fixed-point representation. For details about the numeric element 0 in a floating-point or hexadecimal representation, see the descriptions of the floating-point and hexadecimal elements. ■ When converting numeric data to character string data
■ When converting character string data to numeric data
An error is generated in the following cases.
|
7 |
9 |
Represents a single numeric digit when converting the digits corresponding to a numeric value in the data to be converted. The following description pertains to the numeric element 9 specified in a fixed-point representation. For details about the numeric element 9 in a floating-point or hexadecimal representation, see the descriptions of the floating-point and hexadecimal elements. ■ When converting numeric data to character string data
■ When converting character string data to numeric data
An error is generated in the following cases.
|
|
8 |
Floating-point element |
EEEE eeee |
Specify this element to indicate a floating-point numeric literal. The conversion is the same whether used with 0 or 9 as the numeric element for the digits. The following are examples of number format specifications. Examples
#: Can be specified only when converting character string data to numeric data. ■ When converting numeric data to character string data
■ When converting character string data to numeric data
An error is generated in the following cases.
|
9 |
Sign element |
MI |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
10 |
S (if S is specified at the beginning of the element) |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
|
11 |
S (if S is specified at the end of the element) |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
|
12 |
PR |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
|
13 |
Character string |
"character- string" (character string enclosed in double quotation marks) |
A character string enclosed in double quotation marks (") can be specified at the beginning or end of the number format. Double-byte characters are also permitted. ■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
14 |
Hexadecimal element |
X x |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
An error is generated in the following cases.
|
15 |
Modifier element |
LS |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
16 |
LJ |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
|
17 |
Other |
B |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
18 |
TM TM9 TME |
■ When converting numeric data to character string data
■ When converting character string data to numeric data
|
(c) Rules pertaining to number format
-
The length of the number format cannot exceed 64 bytes.
-
Characters specified in the number format that are not enclosed in double quotation marks (") must be single-byte.
-
Uppercase and lowercase letters are treated the same in number format elements, except in the case of the elements EEEE, X, and character strings enclosed in double quotation marks.
-
When converting character string data to numeric data, numeric elements are typically required in the number format. The exceptions are the hexadecimal element X and the shortest representation elements TM, TM9, TME, which do not require numeric elements.
-
If no numeric element is specified immediately before the decimal point character (.), a numeric element must be specified immediately after the decimal point character (.).
-
If you specify a currency element, decimal point character (.), or B element in the number format, a numeric element must be specified.
-
If you specify a modifier element in the number format, you must specify a numeric element or hexadecimal element X.
-
The sign element S can be specified either before or after a numeric element.
-
The sign elements MI and PR can be specified only after a numeric element.
-
If a sign element is specified, it must be one of the elements S, MI, or PR.
-
The elements listed below can be specified two or more times in the character string specified in the number format. Elements other than these cannot be specified more than once.
-
Comma as a delimiting character element
-
Single-byte space as a delimiting character element
-
Numeric element (0 or 9)
-
"character-string" (character string enclosed in double quotation marks)
-
Hexadecimal element (X or x)
-
(5) Rules
(a) Common rules
-
The data type of the execution result will be the data type specified in post-conversion-data-type.
-
If a dynamic parameter is specified by itself for data-to-convert, post-conversion-data-type will be assumed to be the data type of the dynamic parameter.
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the data to be converted has a null value, or you specify NULL for data-to-convert, the execution result will be a null value.
-
If the data to be converted is character string data with a length of 0 bytes or 0 characters, it is converted as follows:
-
When converting to CHAR type: it is converted to spaces. In the case of CHAR(3), it is converted to '∆∆∆'. ∆ represents a half-width space.
-
When converting to VARCHAR type: it is converted to VARCHAR type data with a length of 0 bytes or 0 characters.
-
When converting to BINARY type: It is converted to X'00'. In the case of BINARY(3), it is converted to X'000000'.
-
When converting to VARBINARY type: It is converted to VARBINARY type data with a length of 0 bytes or 0 characters.
-
In the case of other data types, it is converted to the null value.
-
-
The data types that can be converted (with no format specified) are shown in the following table:
Table 8‒52: Data types that can be converted (with no format specified) Data type of the data to be converted
Post-conversion data type
INTEGER,
SMALLINT
DECIMAL,
DOUBLE PRECISION
CHAR,
VARCHAR
DATE, TIMESTAMP
TIME
BINARY,
VARBINARY
INTEGER,
SMALLINT
Y
Y
Y
Y
N
N
DECIMAL,
DOUBLE PRECISION
Y
Y
Y
N
N
N
CHAR,
VARCHAR
Y
Y
Y
Y
Y
Y
DATE,
TIMESTAMP
Y
N
Y
Y
N
N
TIME
N
N
Y
N
Y
N
BINARY,
VARBINARY
N
N
Y
N
N
Y
- Legend:
-
Y: Can be converted.
N: Cannot be converted.
-
The data types that can be converted (with a format specified) are shown in the following table.
Table 8‒53: Data types that can be converted (with a format specified) Data type of the data to be converted
Post-conversion data type
INTEGER,
SMALLINT
DECIMAL,
DOUBLE PRECISION
CHAR,
VARCHAR
DATE, TIMESTAMP
TIME
BINARY,
VARBINARY
INTEGER,
SMALLINT
N
N
Y#1
N
N
N
DECIMAL,
DOUBLE PRECISION
N
N
Y#1
N
N
N
CHAR,
VARCHAR
Y#1
Y#1
N
Y#2
Y#2
N
DATE,
TIMESTAMP
N
N
Y#2
N
N
N
TIME
N
N
Y#2
N
N
N
BINARY,
VARBINARY
N
N
N
N
N
N
- Legend:
-
Y: Can be converted.
N: Cannot be converted.
- #1:
-
Can be converted when a number format is specified.
- #2:
-
Can be converted when a datetime format is specified.
-
If a format specification is used, the data is first converted according to the specification, and then converted to the post-conversion data type according to the storage assignment rules.
For details about the format specification in the case of the datetime format, see (3) Datetime format elements and rules. For details about the format specification in the case of the number format, see (4) Number format elements and rules.
For details about the storage assignment rules, see (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.
(b) Rules for converting numeric data
- ■ To convert numeric data to numeric data:
-
Conversion of numeric data to numeric data is governed by the rules described in Storage assignment of numeric data in (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.
- ■ To convert character string data to numeric data (with no number format specified):
-
-
Any character string data to be converted (after leading and trailing spaces are removed) must obey the rules for the description format of numeric literals. For the description format rules for numeric literals, see 6.3.2 Description format of literals.
Examples of character string data that can be converted:
'219', '+56', '-3547', '-11.35', '887', 'Δ95Δ'
Examples of character string data that cannot be converted:
'a89', '77g9', '33Δ49'
Legend: Δ: Single-byte space
-
If the character string data item is composed of only spaces, the null value is returned.
-
Once the character string representation of the numeric literal has been converted to a numeric value, it is converted to the post-conversion data type. At that point, it is governed by the rules described in Storage assignment of numeric data in (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.
Example:
CONVERT('11.35',INTEGER) → 11
Once the character string '11.35' has been converted to the DECIMAL type numeric value 11.35, it is converted to an INTEGER type numeric value. At that point, it is governed by the rules for storage assignment of numeric data, which in this case means that the decimal part is truncated.
-
- ■ When converting character string data to numeric data (with a number format specified)
-
-
The format of the character string data to be converted must match the number format specification. However, conversion is possible even when there are single-byte spaces surrounding the number format or the character string data to be converted.
Example:
CONVERT('Δ1,234Δ',INTEGER,'9,999') → 1234
CONVERT('1,234',INTEGER,'Δ9,999Δ') → 1234
Legend: Δ: Single-byte space
-
If the character string data is composed of only a single-byte space, the null value is returned.
-
If there is a character string enclosed in double quotation marks in the number format, that character string, along with any surrounding spaces, is excluded from the character string data that is converted to numeric data according to the number format.
-
Once the character string data has been converted to a numeric value according to number format, it is converted to the post-conversion data type. At that point, the rules described in Storage assignment of numeric data in (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared apply.
Example:
CONVERT('1,000.22',INTEGER,'9,999.99') → 1000
After the character string '1,000.22' is converted to the DECIMAL type numeric value 1000.22, it is converted to an INTEGER type numeric value. At that point, it is governed by the rules for storage assignment of numeric data, which in this case means that the decimal places are truncated.
-
- ■ To convert datetime data to numeric data:
-
Datetime data is converted to the cumulative number of days since January 1, year 1 (CE). In the case of January 1, year 1 (CE), the cumulative number of days is 1. In the case of January 2, year 1 (CE), the cumulative number of days is 2.
Examples:
CONVERT(DATE'0001-01-03',INTEGER) → 3
CONVERT(TIMESTAMP'0001-01-05 11:03:58',INTEGER) → 5
(c) Rules for converting to character string data
The rules for converting to character string data (rules about the length of data) are shown in the following table.
Condition at the time of conversion |
Rules for converting to character string data |
|
---|---|---|
If data of character string type or binary type is converted |
If data of other types is converted |
|
A < B |
If the post-conversion data type is CHAR, it is left-aligned and padded with spaces on the right. |
|
A = B |
The conversion is performed |
|
A > B |
The data is left-aligned and the excess portion on the right is truncated.#1 |
The data cannot be converted. Conversion will result in an error.#2 |
- Legend:
-
A: Length of the source data that is to be converted to character string data
B: Data length of the post-conversion data type
- #1
-
If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.
- #2
-
If the data type of the data to be converted is DOUBLE PRECISION and no number format is specified, the number of decimal places of the mantissa is truncated to fit the data length specified in post-conversion-data-type (rounding to the nearest even number), so no error is generated. However, an error will be generated if the length of the data to be converted exceeds the data length specified in post-conversion-data-type even after all the decimal places of the mantissa have been truncated.
- ■ To convert INTEGER, SMALLINT, or DECIMAL type numeric data to character string data (with no number format specified)
-
-
The result of converting numeric data to the format of a numeric literal is output as character string data. At that point, the results are output in the shortest format that can represent the numeric literal.
However, conversion of DECIMAL type data is performed as follows:
■ The number of digits after the decimal point equals the scaling of the data type of the numeric data, and trailing zeros are not stripped.
■ If the precision of the data type of the numeric data is greater than the scaling, the number of digits in the integer part will not be 0.
■ The decimal point is always added.
Example: +0025.100 → '25.100'
As shown in the example, the plus sign (+) is removed. Any zeros are also stripped from the beginning of the integer part.
-
If the data to be converted is less than 0, it is prefixed with a minus sign (-).
-
- ■ To convert DOUBLE PRECISION type numeric data to character string data (with no number format specified)
-
-
The result of converting numeric data to the format of a floating-point numeric literal is output as character string data. At that point, the results are output in the shortest format that can represent the floating-point numeric literal.
Examples:
+1.0000000000000000E+010 → '1E10'
+3.2000000000000000E+001 → '3.2E1'
+0.1000000000000000E+001 → '1E0'
+0.0000000000000000E+000 → '0E0'
As shown in the examples, the sign is removed from the mantissa and any trailing zeros are removed from the decimal part. Also, the plus sign (+) and leading zeros are removed from the exponent.
-
If the data to be converted is less than 0, it is prefixed with a minus sign (-).
-
Exponents that are less than 0 are prefixed with a minus sign (-).
-
- ■ To convert numeric data to character string data (with a number format specified)
-
-
The numeric data is converted to the format of a numeric literal, and then converted to character string data according to the specified number format.
CONVERT(1000,VARCHAR(6),'LJ$9,999') → '$1,000'
-
If the numeric data cannot be converted according to the number format, it returns the character string padded with hash marks (#). Following the number format, delimiters, currency elements, decimal points, signs, numeric elements, and character strings enclosed in double quotation marks are replaced with hash marks (#). If double-byte characters are specified in the character string, they are replaced with hash marks (#) in proportion to their character size (in bytes).
Example:
CONVERT(1000,CHAR(3),'99') → '###'
-
- ■ To convert datetime data to character string data (with no datetime format specified):
-
-
When datetime data is converted to character string data, it is converted to the format of the predefined output representation. When DATE type data is converted to character string data, it is converted to the format of the predefined output representation of a date. When TIME type data is converted to character string data, it is converted to the format of the predefined output representation of a time. When TIMESTAMP type data is converted to character string data, it is converted to the format of the predefined output representation of a time stamp. For details about the predefined output representations, see 6.3.3 Predefined character-string representations.
Examples:
CONVERT(DATE'2013-06-30',CHAR(10)) → '2013-06-30'
CONVERT(DATE'0001-01-01',CHAR(10)) → '0001-01-01'
CONVERT(TIME'05:33:48.123',CHAR(12)) → '05:33:48.123'
CONVERT(TIMESTAMP'2013-06-30 11:03:58',CHAR(19)) → '2013-06-30 11:03:58'
-
Conversions of datetime data to CHAR(n) or VARCHAR(n) must meet the following conditions:
Data type of the data to be converted
Condition on the post-conversion data length
DATE
n ≥ 10
TIME(p)
When p = 0
n ≥ 8
When p > 0
n ≥ 9 + p
TIMESTAMP(p)
When p = 0
n ≥ 19
When p > 0
n ≥ 20 + p
When n is less than the lengths indicated above, conversion is not possible.
-
When converting DATE type data to CHAR type, if the data length of the post-conversion data is 11 bytes or greater, it is left-aligned and padded with spaces on the right.
Example:
CONVERT(DATE'2013-06-30',CHAR(15)) → '2013-06-30'
Legend: Δ: Single-byte space
-
When converting TIME type data with fractional seconds precision p to CHAR type, if the data length of the post-conversion data is greater than or equal to 10 + p bytes (or greater than or equal to 9 bytes when p = 0), it is left-aligned and padded with spaces on the right.
Example:
CONVERT(TIME'11:03:58.123',CHAR(13)) → '11:03:58.123Δ'
Legend: Δ: Single-byte space
-
When converting TIMESTAMP type data with fractional seconds precision p to CHAR type, if the data length of the post-conversion data is greater than or equal to 21 + p bytes (or greater than or equal to 20 bytes when p = 0), it is left-aligned and padded with spaces on the right.
Example:
CONVERT(TIMESTAMP'2013-06-30 11:03:58',CHAR(20)) → '2013-06-30 11:03:58Δ'
Legend: Δ: Single-byte space
-
- ■ To convert datetime data to character string data (with a datetime format specified):
-
-
Datetime data is converted to character string data according to the specified datetime format.
-
When you specify a datetime format element that is not in the datetime data to be converted, that element is set to a default character string.
Example:
CONVERT(DATE'2013-07-30',CHAR(16),'YYYY/MM/DD HH:MI') → '2013/07/30 00:00'
The datetime data to be converted in the above example is DATE type, which has no time elements, but because time elements (HH and MI) are specified in the datetime format, those portions are set to '00' by default.
The default character strings are shown in the following table:
Table 8‒55: Default character strings for datetime format elements No.
Datetime format element
Default character string
1
Time
HH
'00'
2
HH24
3
HH12
'12'
4
AM/PM
AM
'AM'
5
A.M.
'A.M.'
6
PM
'AM'
7
P.M.
'A.M.'
8
AMN
''
9
PMN
10
Minute
MI
'00'
11
Second
SS
'00'
12
SSSSS
'00000'
13
Fractional seconds
FF1
Any digits to the right of the fractional seconds precision of the target data are padded with zeros.
14
FF2
15
FF3
16
FF4
17
FF5
18
FF6
19
FF7
20
FF8
21
FF9
22
FF10
23
FF11
24
FF12
-
When converting datetime data to CHAR type, if the data length after conversion is less than the data length specified for the post-conversion-data-type, it is left-aligned and padded with spaces on the right.
Example:
CONVERT(DATE'2013-07-30',CHAR(12),'YYYY/MM/DD') → '2013/07/30'
Legend: Δ: Single-byte space
-
If the datetime format element MON, MONTH, DAY, or DY is specified when converting datetime data to character string data, depending on whether the first and second letters of the elements are uppercase or lowercase, the post-conversion character string will vary as follows:
• If the first letter is lowercase, the post-conversion character string will be entirely lowercase.
• If the first letter is uppercase and the second letter is lowercase, the first letter of the post-conversion character string will be uppercase, and the second and subsequent letters will be lowercase.
• If the first and second letters are uppercase, the post-conversion character string will be entirely uppercase.
This is illustrated in the following examples:
Specified datetime format element
Post-conversion character string
mon
'jan'
Mon
'Jan'
MON or MOn
'JAN'
The above examples illustrate the case for January.
-
If you specify FF1 to FF11 in the datetime format and the number of digits in the fractional seconds of the data to be converted exceeds the number of digits specified in the datetime format, the excess digits in the fractional seconds of the datetime format are truncated.
Example:
CONVERT(TIME'15:16:17.123456',CHAR(9),'HHMISS.FF2') → '151617.12'
-
- ■ To convert binary data to character string data
-
-
Only the data type is converted, and the data itself (character encoding itself) is not converted.
Example:
CONVERT(X'61626364',CHAR(4)) ==> 'abcd'
-
If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.
Example:
CONVERT(X'61626364',CHAR(3)) ==> 'abc'
The underlined portion is truncated.
-
If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with half-width spaces on the right.
Example:
CONVERT(X'61626364',CHAR(5)) ==> 'abcd∆'
Legend: ∆: Half-width space
-
(d) Rules for converting to datetime data
- ■ To convert INTEGER or SMALLINT type numeric data to datetime data
-
-
The numeric data is converted to DATE or TIMESTAMP type data based on a starting point of January 1, 0001.
Example:
CONVERT(2,DATE) → DATE'0001-01-02'
-
The time portion of the TIMESTAMP type is converted to 00:00:00, and the fractional seconds are filled with zeros.
Example:
CONVERT(2,TIMESTAMP(3)) → TIMESTAMP'0001-01-02 00:00:00.000'
-
INTEGER and SMALLINT type data in the range 1 to 3652059 can be converted. Values outside this range generate an error.
-
- ■ To convert character string data to datetime data (with no datetime format specified):
-
-
The character string data to be converted (after leading and trailing spaces are removed) can be converted to DATE type data only when it adheres to the predefined input representation format of a date. For details about the predefined input representation of a date, see (a) Predefined input representation in (1) Predefined character-string representation of dates in 6.3.3 Predefined character-string representations.
Example:
CONVERT('2014-07-22',DATE) → DATE'2014-07-22'
Examples of character string data that can be converted:
'2014-06-30', '0001-01-02', '2014-07-30', 'Δ2014/07/30'
Examples of character string data that cannot be converted:
'2013Δ06Δ30', '2013.06.30'
Legend: Δ: Single-byte space
-
The character string data to be converted (after leading and trailing spaces are removed) can be converted to TIME type data only when it adheres to the predefined input representation format of a time. For details about the predefined input representation of a time, see (a) Predefined input representation in (2) Predefined character-string representation of times in 6.3.3 Predefined character-string representations.
Example:
CONVERT('Δ19:46:23.123456',TIME(6)) → TIME'19:46:23.123456'
Examples of character string data that can be converted:
'18:05:22', '10:21:44.123', '10:21:44.123456Δ'
Examples of character string data that cannot be converted:
'18Δ05Δ22', '10:21:44Δ123456'
Legend: Δ: Single-byte space
-
The character string data to be converted (after leading and trailing spaces are removed) can be converted to TIMESTAMP type data only when it adheres to the predefined input representation format of a time stamp. For details about the predefined input representation of a time stamp, see (a) Predefined input representation in (3) Predefined character-string representation of time stamps in 6.3.3 Predefined character-string representations.
Example:
CONVERT('2014/08/02 11:03:58.123456Δ',TIMESTAMP(6)) → TIMESTAMP'2014-08-02 11:03:58.123456'
Examples of character string data that can be converted:
'2014-06-30 11:03:58', '2014/07/30 11:03:58.123', 'Δ2014/07/30 11:03:58.123456789'
Examples of character string data that cannot be converted:
'2014-06-30 11-03-58', '2014/07/30 11:03:58:123456'
Legend: Δ: Single-byte space
-
If the number of digits in the fractional seconds of the character string data to be converted is greater than the number of digits in the fractional seconds of post-conversion-data-type, the fractional seconds beyond the number of digits in the fractional seconds of post-conversion-data-type are truncated.
Example:
CONVERT('19:46:23.123456',TIME(3)) → TIME'19:46:23.123'
-
If the number of digits in the fractional seconds of the character string data to be converted is less than the number of digits in the fractional seconds of post-conversion-data-type, the fractional seconds are padded with zeros as necessary.
Example:
CONVERT('2014-08-02 11:03:58.123',TIMESTAMP(9)) → TIMESTAMP'2014-08-02 11:03:58.123000000'
-
If the character string data item is composed of only spaces, the null value is returned.
-
- ■ To convert character string data to datetime data (with a datetime format specified):
-
-
To convert character string data to DATE type, specify the year, month, and day elements in the datetime format. If you specify other elements (for example, time), they will not affect the results. For details about the elements of the datetime format, see Table 8‒49: Datetime format elements with the same meaning.
-
To convert character string data to TIME type, specify the hour, minute, and second elements in the datetime format. If you specify other elements (for example, the day), they will not affect the results. For details about the elements of the datetime format, see Table 8‒49: Datetime format elements with the same meaning.
-
To convert character string data to TIMESTAMP type, specify the year, month, day, hour, minute, and second elements in the datetime format. For details about the elements of the datetime format, see Table 8‒49: Datetime format elements with the same meaning.
-
Consecutive single-byte spaces are stripped from the beginning and end of the character string data to be converted, and then the data is converted to datetime data according to the datetime format. In addition, parts inside the datetime format that correspond to consecutive single-byte spaces at the beginning or end of the character string data are ignored. Therefore, the following example does not generate an error.
Example:
CONVERT('Δ19Δ46Δ23',TIME(12),'""FMΔHHΔMIΔSSΔFFΔ')
→ TIME'19:46:23.000000000000'
Legend: Δ: Single-byte space
- Note
-
In the above example, the single-byte spaces are handled as follows:
-
Consecutive single-byte spaces at the beginning and end of the character string data to be converted are ignored.
'Δ19Δ46Δ23' → '19Δ46Δ23'
-
Consecutive single-byte spaces at the beginning and end of the datetime format are ignored.
'""FMΔHHΔMIΔSSΔFFΔ' → 'FMΔHHΔMIΔSSΔFF'
The "" part is ignored because it corresponds to consecutive single-byte spaces at the beginning of the character string data. The final single-byte space is ignored because it corresponds to consecutive single-byte spaces at the end of the character string data.
-
Because there are no characters corresponding to FM, the single-byte space after FM corresponds to consecutive single-byte spaces at the beginning of the character string data and is ignored.
'FMΔHHΔMIΔSSΔFF' → 'FMHHΔMIΔSSΔFF'
-
Because there are no fractional seconds in the character string data to be converted, the single-byte space before FF corresponds to consecutive single-byte spaces at the end of the character string data and is ignored.
'FMHHΔMIΔSSΔFF' → 'FMHHΔMIΔSSFF'
If fractional seconds were specified, the single-byte space before FF would not correspond to consecutive single-byte spaces at the end of the character string data.
-
-
If the character string data item is composed of only spaces, the null value is returned.
-
If no fractional second elements are specified in the datetime format, and the data is converted to TIME or TIMESTAMP type data with a fractional seconds precision of 3 or more, the values of the fractional seconds after the conversion will be 0.
Example:
CONVERT('151617',TIME(3),'HHMISS') → TIME'15:16:17.000'
-
The conversion is the same regardless of whether uppercase or lowercase is used for the datetime format elements. Similarly, the conversion is the same regardless of whether uppercase or lowercase is used in the data to be converted. However, uppercase and lowercase are distinguished inside character strings enclosed in double quotation marks (").
-
If FF or one of FF1 to FF12 is specified in the datetime format, the numeric characters in the character string corresponding to the datetime format are extracted during the conversion. At this time, numeric characters are extracted until a non-numeric character is encountered, or until the length associated with the element in the datetime format is reached. If the length of the numeric characters in a character string is shorter than the length associated with the corresponding element in the datetime format, the missing part is converted to 0.
Example:
CONVERT('151617.12',TIME(3),'HHMISS.FF3') → TIME'15:16:17.120'
-
If FF or one of FF1 to FF12 is specified in the datetime format, and the number of digits of fractional seconds in the character string data is less than the fractional seconds precision of the datetime data, the missing fractional seconds are converted to 0.
Example:
CONVERT('151617.123',TIME(6),'HHMISS.FF3') → TIME'15:16:17.123000'
-
If FF or one of FF1 to FF12 is specified in the datetime format, and the number of digits of fractional seconds in the character string data is greater than the fractional seconds precision of the datetime data, the excess fractional seconds in the datetime data are not converted.
Example:
CONVERT('151617.123456',TIME(3),'HHMISS.FF6') → TIME'15:16:17.123'
-
- ■ To convert datetime data to datetime data:
-
The conversion rules for converting datetime data to datetime data are given in the following table.
Table 8‒56: Conversion rules for converting datetime data to datetime data Data type of the data to be converted
Specified post-conversion data type
Conversion rules
DATE
DATE
No conversion is performed.
TIMESTAMP(p2)
-
The time part is converted to 00:00:00.
-
The fractional seconds are filled with zeros.
TIME(p1)
TIME(p2)
-
When p1 = p2
No conversion is performed.
-
When p1 > p2
The fractional seconds beyond p2 are truncated.
-
When p1 < p2
The missing fractional seconds are padded with zeros.
TIMESTAMP(p1)
DATE
Only the date part is converted.
TIMESTAMP(p2)
-
When p1 = p2
No conversion is performed.
-
When p1 > p2
The fractional seconds beyond p2 are truncated.
-
When p1 < p2
The missing fractional seconds are padded with zeros.
- Legend:
-
p1, p2: Fractional seconds precision
-
(e) Rules for converting to binary data
- ■ To convert character string data to binary data
-
-
Only the data type is converted, and the data itself (character encoding itself) is not converted.
Example:
CONVERT('abcd',BINARY(4)) ==> X'61626364'
-
If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.
Example:
CONVERT('abcd',BINARY(3)) ==> X'616263'
The underlined portion is truncated.
If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.
-
If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with X'00' on the right.
Example:
CONVERT('abcd',BINARY(5)) ==> X'6162636400'
-
- ■ To convert binary data to binary data
-
-
If length-of-data-before-type-conversion > length-of-data-after-type-conversion, the excess portion on the right is truncated.
Example:
CONVERT(X'61626364',BINARY(3)) ==> X'616263'
The underlined portion is truncated.
If truncation occurs in the middle of a multi-byte character, part of the multi-byte character is returned as the value of the execution result.
-
If length-of-data-before-type-conversion < length-of-data-after-type-conversion, the results are padded with X'00' on the right.
Example:
CONVERT(X'61626364',BINARY(5)) ==> X'6162636400'
-
(6) Examples
- Example 1:
-
Convert the data in column C2 in table T1 from CHAR type to DATE type and retrieve the rows where column C2 is July 20, 2013.
In column C2, the CHAR type data representing the date is stored in the format MM/DD/YYYY.
SELECT * FROM "T1" WHERE CONVERT("C2",DATE,'MM/DD/YYYY')=DATE'2013-07-20'
- Example 2:
-
Retrieve the rows from table T1 where column C1 is A10101, and convert the data in the corresponding column C2 from INTEGER type to CHAR type. During conversion, prefix the character string with the currency symbol $ and separate every three digits with a comma.
SELECT "C1",CONVERT("C2",CHAR(13),'$999,999,999') FROM "T1" WHERE "C1"='A10101'
- Example 3:
-
In this example, column C2 in table T1 holds CHAR type data representing the price, including the currency symbol $ and commas between every three digits. Convert column C2 from CHAR type to INTEGER type and retrieve the rows for which the discounted price is greater than or equal to $1,000.
SELECT * FROM "T1" WHERE CONVERT("C2",INTEGER,'$9,999')*0.7>=1000