# Data type conversion reference

This topic lists type conversion rules for all data types.

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

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

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

`SMALLFLOAT`

, `FLOAT`

`DECIMAL`

or `MONEY`

to a `TINYINT`

,
`SMALLINT`

, `INTEGER`

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

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

`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"`

`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"
```

`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).`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:`fglrun.decToCharScale2 = true`

`fglrun.decToCharScale2`

configuration parameter, 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.

`FLOAT/SMALLFLOAT`

-to-string conversion must round to 2 digits, use the
`fglrun.floatToCharScale2`

FGLPROFILE
entry:`fglrun.floatToCharScale2 = true`

`fglrun.floatToCharScale2`

configuration parameter, unless you have migration
issues.## 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.

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