Data type conversion reference
This topic lists type conversion rules for all data types.
Controlling type conversion errors
Type conversion errors can be controlled with the WHENEVER ANY ERROR
clause, or
with TRY/CATCH
blocks. For more details, see Handling type conversion errors.
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
becomes 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 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.
Character string to character string types
When assigning a CHAR/VARCHAR
value to a CHAR/VARCHAR
variable,
the trailing characters are silently truncated, if the receiving type is not large enouth.
Not runtime error can be detected, even with WHENEVER ANY ERROR
or
TRY/CATCH
.
MAIN
DEFINE c6 CHAR(6)
DEFINE c3 CHAR(3)
WHENEVER ANY ERROR CONTINUE
LET c6 = "abcdef"
LET c3 = c6
DISPLAY c3, " : ", status -- Shows "abc : 0"
END MAIN
Integers to integers types
Converting between TINYINT
, SMALLINT
, INTEGER
and BIGINT
is possible, as long as the receiving type can hold the whole number to
be assigned.
If the integer value exceeds the range of the receiving data type, the conversion fails.
MAIN
DEFINE int4 INTEGER
DEFINE int2 SMALLINT
WHENEVER ANY ERROR CONTINUE
LET int4 = 32767
LET int2 = int4
DISPLAY int2, " : ", status
LET int4 = 32768
LET int2 = int4
DISPLAY int2, " : ", status -- Shows error -1214
END MAIN
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, the conversion fails.
MAIN
DEFINE int4 INTEGER
DEFINE d051 DECIMAL(5,1)
WHENEVER ANY ERROR CONTINUE
LET int4 = 999
LET d051 = int4
DISPLAY d051, " : ", status -- Shows 999.0
LET int4 = 999999
LET d051 = int4
DISPLAY d051, " : ", status -- Shows error -1226
END MAIN
Decimal to integer types
When converting a SMALLFLOAT
, FLOAT
DECIMAL
or
MONEY
to a TINYINT
, SMALLINT
, INTEGER
or BIGINT
, the fractional part of the decimal value is truncated.
If the decimal value exceeds the range of the receiving integer data type, the conversion fails.
MAIN
DEFINE d102 DECIMAL(10,2)
DEFINE int2 SMALLINT
WHENEVER ANY ERROR CONTINUE
LET d102 = 32767.99
LET int2 = d102
DISPLAY int2, " : ", status -- Shows 32767
LET d102 = 32768.0
LET int2 = d102
DISPLAY int2, " : ", status -- Shows error -1214
END MAIN
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.
If the decimal value exceeds the range of the receiving decimal data type, the conversion fails.
MAIN
DEFINE d102 DECIMAL(10,2)
DEFINE d051 DECIMAL(5,1)
WHENEVER ANY ERROR CONTINUE
LET d102 = 123.45
LET d051 = d102
DISPLAY d051, " : ", status -- Shows 123.5
LET d102 = 99999.99
LET d051 = d102
DISPLAY d051, " : ", status -- Shows error -1226
END MAIN
Integers to character string types
Converting TINYINT
, SMALLINT
, INTEGER
or
BIGINT
values to CHAR
, VARCHAR
and
STRING
is allowed as long as the receiving character variable is large enough.
If the receiving character type is to small to hold the integer value, it is filled with
*
overflow stars, and the conversion fails.
MAIN
DEFINE int4 INTEGER
DEFINE vc05 VARCHAR(5)
WHENEVER ANY ERROR CONTINUE
LET int4 = -123
LET vc05 = int4
DISPLAY vc05, " : ", status
LET int4 = 999999
LET vc05 = int4
DISPLAY vc05, " : ", status -- Shows ***** and error -1207
END MAIN
Decimal to character string 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. Especially, the decimal separator is defined by these environment variables.
util.JSON.stringify()
method:IMPORT util
FUNCTION dec2iso(d DECIMAL)
RETURN util.JSON.stringify(d)
END FUNCTION
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).
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 forDECIMAL(p,s)
, p+7 forDECIMAL(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 DECIMAL(P)
has been revised with Genero 2.50. If
DECIMAL(P)
-to-string conversion must round to 2 digits, use the
fglrun.decToCharScale2
FGLPROFILE entry (this applies to all contexts):
fglrun.decToCharScale2 = true
Another FGLPROFILE entry can be used, to get the 2-digit rounding of DECIMAL(P)
only in the context of the PRINT
statement in reports. (the
fglrun.decToCharScale2
and fglrun.decToCharScale2.print
parameters
are mutually exclusive):
fglrun.decToCharScale2.print = true
Do not use the fglrun.decToCharScale2*
configuration parameters, unless
you have migration issues.
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.
If FLOAT/SMALLFLOAT
-to-string conversion must round to 2 digits, use the
fglrun.floatToCharScale2
FGLPROFILE entry (this applies to all
contexts):
fglrun.floatToCharScale2 = true
Another FGLPROFILE entry can be used, to get the 2-digit rounding of
FLOAT/SMALLFLOAT
only in the context of the PRINT
statement
in reports. (the fglrun.floatToCharScale2
and
fglrun.floatToCharScale2.print
parameters are mutually exclusive):
fglrun.floatToCharScale2.print = true
Do not use the fglrun.floatToCharScale2*
configuration parameters,
unless you have migration issues.
Character string to numeric 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.
Numeric formatting is controlled by the DBMONEY and DBFORMAT environment variables. Especially, the decimal separator is defined by these environment variables.
util.JSON.parse()
method:IMPORT util
FUNCTION iso2dec(s STRING)
DEFINE d DECIMAL
CALL util.JSON.parse(s,d)
RETURN d
END FUNCTION
If the original value contains more significant digits or more fractional digits than the receiving data type supports, low-order digits are discarded.
Example using a DECIMAL
:
MAIN
DEFINE d102 DECIMAL(10,2)
WHENEVER ANY ERROR CONTINUE
LET d102 = "-123.45"
DISPLAY d102, " : ", status
LET d102 = "1234567890123.45"
DISPLAY d102, " : ", status -- Shows error -1226
LET d102 = "12345678.999"
DISPLAY d102, " : ", status -- Shows 12345679.00
END MAIN
Example using an INTEGER
:
MAIN
DEFINE int4 INTEGER
WHENEVER ANY ERROR CONTINUE
LET int4 = "-12345"
DISPLAY int4, " : ", status
LET int4 = "999999999999"
DISPLAY int4, " : ", status -- Shows error -1215
END MAIN
Date time to character string types
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 an
INTERVAL
to a string, eitherYYYY-MM
orDD hh:mm:ss.fffff
standard formats are used, depending on the interval class. - When converting a
DATETIME
to a string, theYYYY-MM-DD hh:mm:ss.fffff
standard format is used.
If the conversion result is longer than the receiving variable, the resulting character string is
filled with *
overflow stars.
MAIN
DEFINE da DATE
DEFINE vc20 VARCHAR(20)
DEFINE vc05 VARCHAR(5)
WHENEVER ANY ERROR CONTINUE
LET da = MDY(12,24,2012)
LET vc20 = da
DISPLAY vc20, " : ", status -- Shows 12/24/2012
LET vc05 = da
DISPLAY vc05, " : ", status -- Shows *****
END MAIN
See also Formatting DATE values, Formatting DATETIME values and Formatting INTERVAL values.
Character string to date time types
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 an
INTERVAL
, the source string must be specified either inYYYY-MM
orDD hh:mm:ss.fffff
standard formats, depending on the interval class of the target variable.When converting a string to a
DATETIME
, the format must beYYYY-MM-DD hh:mm:ss.fffff
, or must follow the ISO 8601 format sub-set (with the T separator between the date and time part, and optional UTC indicator or timezone offset+/-hh:mm
):- The ISO 8601 standard requires a
T
separator between the date and time part. However, it is possible that date/time values with timezone offset might deviate from ISO 8601, by using a space instead of theT
separator. If a timezone offset is specified, the next conversion rules will apply, even if noT
separator is used. - A
"Z"
character after the time part specifies the zone designator for the zero UTC offset. When"Z"
is used in the source string, the UTC date/time is converted to a local time. - When the string ends with a "
+/-hh:mm
" timezone offset, the value is adjusted to the local timezone: A negative timezone offset such as-02:00
indicates that the represented datetime value is in a timezone west ofUTC±00:00
. A positive timezone offset such as+05:00
indicates that the represented datetime value is in a timezone east ofUTC±00:00
. When "+/-hh:mm
" is used in the source string, the date/time with timezone offset is converted to a local time.
- The ISO 8601 standard requires a
DATETIME
: IMPORT util
MAIN
DISPLAY "Current Time Zone (TZ):"
RUN 'date +"%Z %:z"'
DISPLAY "Current date/time: ", CURRENT, " / UTC:", util.Datetime.getCurrentAsUTC()
DISPLAY "Source", COLUMN 30, "Local Time", COLUMN 60, "UTC"
DISPLAY "With December date (winter):"
CALL test("2012-12-24 11:33")
CALL test("2012-12-24T11:33+01:00")
CALL test("2012-12-24 11:33+01:00")
CALL test("2012-12-24T10:33Z")
CALL test("2012-12-24 10:33Z")
DISPLAY "With July date (DST applies):"
CALL test("2012-07-24 11:33")
CALL test("2012-07-24T11:33+01:00")
CALL test("2012-07-24 11:33+01:00")
CALL test("2012-07-24T10:33Z")
CALL test("2012-07-24 10:33Z")
END MAIN
FUNCTION test(s STRING)
DEFINE dt DATETIME YEAR TO MINUTE
LET dt = s
DISPLAY s, COLUMN 30, dt, COLUMN 60, util.Datetime.toUTC(dt)
END FUNCTION
Output example (Linux):$ TZ="Europe/Paris" fglrun dtconv.42m
Current Time Zone (TZ):
CEST +02:00
Current date/time: 2023-09-05 08:02:50.054 / UTC:2023-09-05 06:02:50.05478
Source Local Time UTC
With December date (winter):
2012-12-24 11:33 2012-12-24 11:33 2012-12-24 10:33
2012-12-24T11:33+01:00 2012-12-24 11:33 2012-12-24 10:33
2012-12-24 11:33+01:00 2012-12-24 11:33 2012-12-24 10:33
2012-12-24T10:33Z 2012-12-24 11:33 2012-12-24 10:33
2012-12-24 10:33Z 2012-12-24 11:33 2012-12-24 10:33
With July date (DST applies):
2012-07-24 11:33 2012-07-24 11:33 2012-07-24 09:33
2012-07-24T11:33+01:00 2012-07-24 12:33 2012-07-24 10:33
2012-07-24 11:33+01:00 2012-07-24 12:33 2012-07-24 10:33
2012-07-24T10:33Z 2012-07-24 12:33 2012-07-24 10:33
2012-07-24 10:33Z 2012-07-24 12:33 2012-07-24 10:33
$ TZ="America/Los_Angeles" fglrun dtconv.42m
Current Time Zone (TZ):
PDT -07:00
Current date/time: 2023-09-04 23:03:35.551 / UTC:2023-09-05 06:03:35.55135
Source Local Time UTC
With December date (winter):
2012-12-24 11:33 2012-12-24 11:33 2012-12-24 19:33
2012-12-24T11:33+01:00 2012-12-24 02:33 2012-12-24 10:33
2012-12-24 11:33+01:00 2012-12-24 02:33 2012-12-24 10:33
2012-12-24T10:33Z 2012-12-24 02:33 2012-12-24 10:33
2012-12-24 10:33Z 2012-12-24 02:33 2012-12-24 10:33
With July date (DST applies):
2012-07-24 11:33 2012-07-24 11:33 2012-07-24 18:33
2012-07-24T11:33+01:00 2012-07-24 03:33 2012-07-24 10:33
2012-07-24 11:33+01:00 2012-07-24 03:33 2012-07-24 10:33
2012-07-24T10:33Z 2012-07-24 03:33 2012-07-24 10:33
2012-07-24 10:33Z 2012-07-24 03:33 2012-07-24 10:33
See also util.Date.parse, util.Datetime.parse and util.Interval.parse.
Converting DATE
to/from DATETIME
types
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 aDATETIME
, 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 aDATE
, an implicitEXTEND( datetime-value, YEAR TO DAY )
is performed.
MAIN
DEFINE da DATE
DEFINE dt1 DATETIME YEAR TO SECOND
DEFINE 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.