8.12.5 CONVERT

(a) Datetime format elements

The table below shows the elements that can be specified in the datetime format.

#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

1. 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.

2. 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).

3. 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

(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.

(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)

(a) Common rules

1. The data type of the execution result will be the data type specified in post-conversion-data-type.

2. 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.

3. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

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:

  1. 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'

  2. 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.

  3. 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'

  4. 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'