3.10. Conversion of Data Types
When composing an expression or specifying an operation, the aim should be to use compatible data types for the operands. When a need arises to use a mixture of data types, it should prompt you to look for a way to convert incompatible operands before subjecting them to the operation. The ability to convert data may well be an issue if you are working with Dialect 1 data.
3.10.1. Explicit Data Type Conversion
The CAST function enables explicit conversion between many pairs of data types.
Syntax
|
CAST (<expression> AS <target_type>)
|
|<target_type> ::= <domain_or_non_array_type> | <array_datatype>
|
|<domain_or_non_array_type> ::=
| !! See Scalar Data Types Syntax !!
|
|<array_datatype> ::=
| !! See Array Data Types Syntax !!
See also CAST() in Chapter Built-in Scalar Functions.
3.10.1.1. Casting to a Domain
When you cast to a domain, any constraints declared for it are taken into account, i.e. NOT NULL or CHECK constraints.
If the value does not pass the check, the cast will fail.
If TYPE OF is additionally specified — casting to its base type — any domain constraints are ignored during the cast.
If TYPE OF is used with a character type (CHAR/VARCHAR), the character set and collation are retained.
3.10.1.2. Casting to TYPE OF COLUMN
When operands are cast to the type of a column, the specified column may be from a table or a view.
Only the type of the column itself is used. For character types, the cast includes the character set, but not the collation. The constraints and default values of the source column are not applied.
Example
|
CREATE TABLE TTT (
| S VARCHAR (40)
| CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
|);
|COMMIT;
|
|SELECT
| CAST ('I have many friends' AS TYPE OF COLUMN TTT.S)
|FROM RDB$DATABASE;
3.10.1.3. Conversions Possible for the CAST Function
| From Data Type | To Data Type |
|---|---|
Numeric types | Numeric types, |
|
|
|
|
|
|
|
|
|
|
To convert string data types to the BOOLEAN type, the value must be (case-insensitive) 'true' or 'false', or NULL.
Keep in mind that partial information loss is possible.
For instance, when you cast the TIMESTAMP data type to the DATE data type, the time-part is lost.
3.10.1.4. Datetime Formats
To cast string data types to the DATE, TIME or TIMESTAMP data types, you need the string argument to be one of the predefined datetime mnemonics (see Table 3.16, “Predefined Datetime Mnemonics”) or a representation of the date in one of the allowed datetime formats (see Datetime Format Syntax),
Literal | Description |
| Current date and time |
| Current date |
| Current date + 1 (day) |
| Current date - 1 (day) |
Casting the date mnemonics 'TODAY', 'TOMORROW' or 'YESTERDAY' to a TIMESTAMP WITH TIME ZONE will produce a value at 00:00:00 UTC rebased to the session time zone.
For example, cast('TODAY' as timestamp with time zone) on 2021-05-02 20:00 - 2021-05-03 19:59 New York (or 2021-05-03 00:00 - 2021-05-03 23:59 UTC) with session time zone America/New_York, will produce a value TIMESTAMP '2021-05-02 20:00:00.0000 America/New_York', while cast('TODAY' as date) or CURRENT_DATE will produce either DATE '2021-05-02' or DATE '2021-05-03' depending on the actual date.
Sample Date Literal Interpretations
|
select
| cast('04.12.2014' as date) as d1, -- DD.MM.YYYY
| cast('04 12 2014' as date) as d2, -- MM DD YYYY
| cast('4-12-2014' as date) as d3, -- MM-DD-YYYY
| cast('04/12/2014' as date) as d4, -- MM/DD/YYYY
| cast('04.12.14' as date) as d5, -- DD.MM.YY
| -- DD.MM with current year
| cast('04.12' as date) as d6,
| -- MM/DD with current year
| cast('04/12' as date) as d7,
| cast('2014/12/04' as date) as d8, -- YYYY/MM/DD
| cast('2014 12 04' as date) as d9, -- YYYY MM DD
| cast('2014.12.04' as date) as d10, -- YYYY.MM.DD
| cast('2014-12-04' as date) as d11, -- YYYY-MM-DD
| cast('4 Jan 2014' as date) as d12, -- DD MM YYYY
| cast('2014 Jan 4' as date) as dt13, -- YYYY MM DD
| cast('Jan 4 2014' as date) as dt14, -- MM DD YYYY
| cast('11:37' as time) as t1, -- HH:mm
| cast('11:37:12' as time) as t2, -- HH:mm:ss
| cast('11:31:12.1234' as time) as t3, -- HH:mm:ss.nnnn
| -- DD.MM.YYYY HH:mm
| cast('04.12.2014 11:37' as timestamp) as dt1,
| -- MM/DD/YYYY HH:mm:ss
| cast('04/12/2014 11:37:12' as timestamp) as dt2,
| -- DD.MM.YYYY HH:mm:ss.nnnn
| cast('04.12.2014 11:31:12.1234' as timestamp) as dt3,
| cast('now' as timestamp) as m1,
| cast('today' as date) as m2,
| cast('yesterday' as date) as m3,
| cast('tomorrow' as date) as m4
|from rdb$database
3.10.1.5. Shorthand Casts for Datetime Data Types
Firebird allows the use of a shorthand C-style
type syntax for casts from string to the types DATE, TIME and TIMESTAMP.
The SQL standard calls these datetime literals
.
Syntax
|<data_type> 'date_format_string'
See also Datetime Literals.
These literal expressions are evaluated directly during parsing, as though the statement were already prepared for execution.
As this produced unexpected or confusing results when using the datetime mnemonics like 'NOW', especially in PSQL code, the datetime mnemonics are no longer allowed in datetime literals since Firebird 4.0.
To use datetime mnemonics, use the full CAST syntax.
An example of using such an expression in a trigger:
|NEW.CHANGE_DATE = CAST('now' AS TIMESTAMP);
3.10.2. Implicit Data Type Conversion
Implicit data conversion is not possible in Dialect 3 — the CAST function is almost always required to avoid data type clashes.
In Dialect 1, in many expressions, one type is implicitly cast to another without the need to use the CAST function. For instance, the following statement in Dialect 1 is valid:
|UPDATE ATABLE|SET ADATE = '25.12.2016' + 1
The string literal will be cast to the DATE type implicitly.
In Dialect 3, this statement will raise error 35544569, Dynamic SQL Error: expression evaluation not supported, Strings cannot be added or subtracted in dialect 3
— a cast will be needed:
|UPDATE ATABLE|SET ADATE = CAST ('25.12.2016' AS DATE) + 1
Or, with a datetime literal:
|UPDATE ATABLE|SET ADATE = DATE '25.12.2016' + 1
In Dialect 1, mixing integer data and numeric strings is usually possible because the parser will try to cast the string implicitly. For example,
|2 + '1'
will be executed correctly.
In Dialect 3, an expression like this will raise an error, so you will need to write it as a CAST expression:
|2 + CAST('1' AS SMALLINT)
The exception to the rule is during string concatenation.
3.10.2.1. Implicit Conversion During String Concatenation
When multiple data elements are being concatenated, all non-string data will undergo implicit conversion to string, if possible.
Example
|
SELECT 30||' days hath September, April, June and November' CONCAT$
| FROM RDB$DATABASE;
|
|CONCAT$
|------------------------------------------------
|30 days hath September, April, June and November