Data type conversion reference

Boolean type conversions

A BOOLEAN value is an integer value 1 or 0 and thus can be converted to/from any other numeric type of the language.

When converting a numeric value to a BOOLEAN, any value different from 0 will become TRUE, otherwise (zero) is FALSE.

DEFINE hasContent BOOLEAN, s STRING
LET s = "abc"
LET hasContent = s.getLength()

When converting a string (CHAR, VARCHAR or STRING) to BOOLEAN, the string will be converted to a number first, then the number to boolean conversion applies. If the string value cannot convert to a numeric value (for example, "abc"), the boolean value will be NULL.

When converting a BOOLEAN to a string, the result will be "1" or "0" string values according to the boolean value.

Large object type conversions

A TEXT value can be converted to / from CHAR, VARCHAR or STRING.

The BYTE type cannot be converted to/from any other type.

Integers to decimal types

TINYINT, SMALLINT, INTEGER and BIGINT values can be converted to SMALLFLOAT, FLOAT DECIMAL or MONEY as long as the decimal type is defined with sufficient digits to hold the whole number.

If the integer value exceeds the range of the receiving data type, an overflow error occurs.

Decimal to integer types

When converting a SMALLFLOAT, FLOAT DECIMAL or MONEY to a TINYINT, SMALLINT, INTEGER and BIGINT, the fractional part of the decimal value is truncated.

MAIN
  DEFINE d DECIMAL(10,2),
         i INTEGER
  LET d = 123.45
  LET i = d
  DISPLAY i   -- displays 123
END MAIN

If the decimal value exceeds the range of the receiving integer data type, an overflow error occurs.

Decimal to decimal types

Converting between SMALLFLOAT, FLOAT DECIMAL or MONEY types is allowed as long as the receiving type is defined with sufficient digits to hold the whole part of the original value.

If the original value contains more fractional digits than the receiving data type supports, low-order digits are discarded.

MAIN
  DEFINE d1 DECIMAL(10,2),
         d2 DECIMAL(5,1)
  LET d1 = 123.45
  LET d2 = d1
  DISPLAY d2    -- displays 123.5
END MAIN

Decimal to character types

Converting a SMALLFLOAT, FLOAT DECIMAL or MONEY values to CHAR, VARCHAR and STRING implies numeric formatting.

Numeric formatting is controlled by the DBMONEY and DBFORMAT environment variables.

According to the conversion context, the resulting string is left-aligned (for lossless conversions) or right-aligned (for visual conversions), and the decimal part is kept, according to the numeric type.

MAIN
  DEFINE m MONEY(8,2),
         s VARCHAR(10)
  LET m = 123.45
  LET s = m   -- Lossless conversion "$123.45"
  DISPLAY m   -- Visual conversion   "    $123.45"
END MAIN

Fixed point decimals (DECIMAL(p,s)) are converted to strings that can fit in a CHAR(p+2): The string is build with up to p significant digits + 1 character for the sign + 1 character for the decimal point. The result of a DECIMAL(p,s) to string conversion is never longer than p + 2 characters. For example, a DECIMAL(5,2) can produce "-999.99" (5 + 2 = 7c).

Floating point decimals (DECIMAL(p)) are converted to strings that can fit in a CHAR(p+7): The string is build with up to p significant digits + 1 character for the sign + 1 character for the decimal point + the length of the exponant of needed ("e+123"). The result of a DECIMAL(p) to string conversion is never longer than p + 7. For example, a DECIMAL(5) can produce "-1.2345e-123" (5 + 7 = 12c).

DECIMAL to string conversion depends on the context in which the conversion occurs:

Visual conversion: The result of this conversion will typically be presented to the end user. This conversion happens in DISPLAY, MESSAGE, ERROR, PRINT. The result of a visual conversion is right aligned (padded with leading blanks). This padding results in the same length for any value for a given decimal precision. The length of the result is the maximum possible length as described previously (p+2 for DECIMAL(p,s), p+7 for DECIMAL(p)).
Visual conversion examples for DECIMAL(5,2):
```
   Values     1234567
----------------------
   0       | "   0.00"
   -999.99 | "-999.99"
   12.3    | "  12.30"
   12.34   | "  12.34"
```
Visual conversion examples for DECIMAL(5):
```
   Values     123456789012
---------------------------
   0       | "         0.0"
   -99999  | "    -99999.0"
   12.3    | "        12.3"
   12.34   | "       12.34"
   12.345  | "      12.345"
   1.23e7  | "  12300000.0"
   1e100   | "       1e100"
```
Form field conversion: This conversion concerns decimal numbers presented in form-fields. The result of this conversion is in best case the same as (1). The result of the conversion depends on the width of the form-field. If the width of the form-field is smaller than the perfect length, automatic rounding and exponential notation might be used.
Lossless conversion: Such conversion happens when assigning numbers to string variables (LET), passing numbers as parameters to functions expecting strings, returning numbers from functions to strings, serializing numbers (UNLOAD, XML or JSON APIs). These conversions must avoid the loss of significant digits. When using floating point decimals, this leads to a variable length of the resulting string. A conversion must be reversible: decimal to string to decimal must give the original value. If the target variable is shorter then the maximum possible length, then automatic rounding will occur.
Lossless conversion examples of DECIMAL(5,2):
```
   Values     1234567
----------------------
   0       | "0.00"
   -999.99 | "-999.99"
   12.3    | "12.30"
   12.34   | "12.34"
```
Lossless conversion examples of DECIMAL(5):
```
   Values     123456789012
---------------------------
   0       | "0.0"
   -99999  | "-99999.0"
   12.3    | "12.3"
   12.34   | "12.34"
   12.345  | "12.345"
   1.23e7  | "12300000.0"
   1e100   | "1e100"
```

Automatic rounding occurs if the target string variable is shorter than the maximum possible length of the DECIMAL type. Such conversion might loose significant digits: The runtime system tries to round the value, to fit into the target variable.

  Values     Different target sizes  
              123456   12345   1234
------------------------------------
  0.98765  | "0.9877" "0.988" "0.99"
  123.45   | "123.45" "123.5" "123"

Automatic switch to the exponential notation will occur if the integer part of the decimal value does not fit into the target string variable. For example, if the source variable is a DECIMAL(12) and the target variable is a CHAR(9):

  Values         123456789
----------------------------
  1234567     | "1234567.0"
  12345678    | "12345678"
  123456789   | "123456789"
  1234567890  | "1.2346e10"
  12345678901 | "1.2346e11"

The exponential notation will also be used if the absolute value of a floating point decimal is less than 1e-8 (0.00000001).

Default formatting of floating point decimals has been reviewed in 2.50. If DECIMAL(P) to string conversion must round to 2 digits, use the fglrun.decToCharScale2 FGLPROFILE entry:

fglrun.decToCharScale2 = true

Formatting a FLOAT is the same as DECIMAL(16). Any FLOAT value with up to 15 digits is exact. There is no precision loss when converting an exact FLOAT back and forth to/form a string. Some FLOAT values require 16, in some rare cases 17 digits for an exact string representation. 16 and 17 digits are not always exact: "8.000000000000001" and "8.000000000000002" represent the same float value.

Formatting a SMALLFLOAT is the same as DECIMAL(7). Any SMALLFLOAT value with up to 6 digits is exact. There is no precision loss when converting an exact SMALLFLOAT back and forth to/form a string. Some SMALLFLOAT values require 7, in some rare cases 8 digits for an exact string representation. 7 and 8 digits SMALLFLOAT are not always exact: "0.0009999901" and "0.0009999902" represent the same SMALLFLOAT value.

Character to decimal types

A CHAR, VARCHAR and STRING value can be converted to a TINYINT, SMALLINT, INTEGER, BIGINT, SMALLFLOAT, FLOAT DECIMAL or MONEY value as long as the character string value represents a valid number.

If the original value contains more significant digits or more fractional digits than the receiving data type supports, low-order digits are discarded.

MAIN
  DEFINE d DECIMAL(10,2)
  LET d = "-123.45"
  DISPLAY d   -- displays -123.45
  LET d = "1234567890123.45"
  DISPLAY d   -- displays null
  LET d = "12345678.999"
  DISPLAY d   -- displays 12345679.00
END MAIN

Date time to character types

Converting DATE, DATETIME and INTERVAL values to CHAR, VARCHAR and STRING implies date time formatting.

DATE formatting is controlled by the DBDATE environment variable.

When converting a DATETIME to a string, the YYYY-MM-DD hh:mm:ss.fffff standard format is used.

When converting an INTERVAL to a string, either YYYY-MM or DD hh:mm:ss.fffff standard formats are used, according to the interval class.

If the resulting is longer than the receiving variable, the resulting character string is null.

MAIN
  DEFINE d DATE,
         s VARCHAR(20),
         v VARCHAR(5)
  LET d = MDY(12,24,2012)
  LET s = d
  DISPLAY s   -- displays 12/24/2012
  LET v = d
  DISPLAY v   -- displays null
END MAIN

Character to date time types

Converting a CHAR, VARCHAR or STRING value to a DATE, DATETIME or INTERVAL is possible as long as the character string defines a well formatted date time or interval value.

When converting a character string to a DATE, the string must follow the date format defined by the DBDATE environment variable.

When converting a string to a DATETIME, the format must be YYYY-MM-DD hh:mm:ss.fffff or follow the ISO 8601 format sub-set (with the T separator between the date and time part, and with optional UTC indicator or timezone offset)

MAIN
  DEFINE dt DATETIME YEAR TO SECOND
  LET dt = "2012-12-24 11:33:45"
  DISPLAY dt   -- displays 2012-12-24 11:33:45
  LET dt = "2012-12-24T11:33:45+01:00"
  DISPLAY dt   -- displays 2012-12-24 11:33:45 (if TZ=UTC+1h)
  LET dt = "2012-12-24T10:33:45Z"
  DISPLAY dt   -- displays 2012-12-24 11:33:45 (if TZ=UTC+1h)
END MAIN

Converting `DATE` to/from `DATETIME` types

When converting a DATETIME to another DATETIME with a different precision, truncation from the left or right can occur. When then target type has more fields as the source type, the year, month and day fields are filled with the current date.

When converting a DATE to a DATETIME, the datetime fields are filled with year, month and day from the date value and time fields are set to zero.

When converting a DATETIME to a DATE, an implicit EXTEND( datetime-value, YEAR TO DAY ) is performed.

MAIN
  DEFINE da DATE,
         dt1 DATETIME YEAR TO SECOND,
         dt2 DATETIME HOUR TO MINUTE
  LET da = MDY(12,24,2012)
  LET dt1 = da
  DISPLAY dt1   -- displays 2012-12-24 00:00:00
  LET dt2 = "23:45"
  LET dt1 = dt2
  DISPLAY dt1   -- displays <current date> 00:00:00
END MAIN

Unsupported type conversions

Other data type conversions not mentioned in this topic are not allowed and will result in a runtime error.