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.

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

When converting a BOOLEAN to a string, the result will be "1" or "0" string values, depending on 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

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.

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

The resulting string is left-aligned (for lossless conversions) or right-aligned (for visual conversions), depending on the conversion context; and the decimal part is kept depending on 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 built 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 built with up to p significant digits + 1 character for the sign + 1 character for the decimal point + the length of the exponent 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).

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

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

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.

fglrun.floatToCharScale2 = true

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, depending on the interval class.

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. If the target type has more fields than 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.

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.