3.8. 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.8.1. Explicit Data Type Conversion

The CAST function enables explicit conversion between many pairs of data types.

Syntax

  1. CAST ( { <value> | NULL } AS <data_type>)
  2. <data_type> ::=
  3. <sql_datatype>
  4. | [TYPE OF] domain
  5. | TYPE OF COLUMN relname.colname
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.

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

  1. CREATE TABLE TTT (
  2. S VARCHAR (40)
  3. CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
  4. );
  5. COMMIT;
  6. SELECT
  7. CAST ('I have many friends' AS TYPE OF COLUMN TTT.S)
  8. FROM RDB$DATABASE;
Conversions Possible for the CAST Function
Table 8. Conversions with CAST
From Data TypeTo Data Type

Numeric types

Numeric types, [VAR]CHAR, BLOB

[VAR]CHAR

[VAR]CHAR, BLOB, Numeric types, DATE, TIME, TIMESTAMP

BLOB

[VAR]CHAR, BLOB, Numeric types, DATE, TIME, TIMESTAMP

DATE, TIME

[VAR]CHAR, BLOB, TIMESTAMP

TIMESTAMP

[VAR]CHAR, BLOB, DATE, TIME

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.

Literal 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 date and time literals (see Table 9) or a representation of the date in one of the allowed date-time literal formats:

  1. <timestamp_format> ::=
  2. { MM<p>DD[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]
  3. | MM<p>DD[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]]
  4. | DD<p>MM[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]]
  5. | MM<p>DD[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]]
  6. | DD<p>MM[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]]
  7. | NOW
  8. | TODAY
  9. | TOMORROW
  10. | YESTERDAY }
  11. <date_format> ::=
  12. { MM<p>DD
  13. | MM<p>DD[<p>YYYY]
  14. | DD<p>MM[<p>YYYY]
  15. | MM<p>DD[<p>YY]
  16. | DD<p>MM[<p>YY]
  17. | TODAY
  18. | TOMORROW
  19. | YESTERDAY }
  20. <time_format> :=
  21. { HH[<p>mm[<p>SS[<p>NNNN]]]
  22. | NOW }
  23. <p> ::= whitespace | . | : | , | - | /
Table 9. Date and Time Literal Format Arguments
ArgumentDescription

timestamp_format

Format of timestamp literal

date_literal

Format of date literal

time_literal

Format of time literal

YYYY

Four-digit year

YY

Two-digit year

MM

Month. It may contain 1 or 2 digits (1-12 or 01-12). You can also specify the three-letter shorthand name or the full name of a month in English. Case-insensitive

DD

Day. It may contain 1 or 2 digits (1-31 or 01-31)

HH

Hour. It may contain 1 or 2 digits (0-23 or 00-23)

mm

Minutes. It may contain 1 or 2 digits (0-59 or 00-59)

SS

Seconds. It may contain 1 or 2 digits (0-59 or 00-59)

NNNN

Ten-thousandths of a second. It may contain from 1 to 4 digits (0-9999)

p

A separator, any of permitted characters. Leading and trailing spaces are ignored

Table 10. Literals with Predefined Values of Date and Time

Literal

Description

Data Type

Dialect 1

Dialect 3

‘NOW’

Current date and time

DATE

TIMESTAMP

‘TODAY’

Current date

DATE with zero time

DATE

‘TOMORROW’

Current date + 1 (day)

DATE with zero time

DATE

‘YESTERDAY’

Current date - 1 (day)

DATE with zero time

DATE

Use of the complete specification of the year in the four-digit form — YYYY — is strongly recommended, to avoid confusion in date calculations and aggregations.

Sample Date Literal Interpretations

  1. select
  2. cast('04.12.2014' as date) as d1, -- DD.MM.YYYY
  3. cast('04 12 2014' as date) as d2, -- MM DD YYYY
  4. cast('4-12-2014' as date) as d3, -- MM-DD-YYYY
  5. cast('04/12/2014' as date) as d4, -- MM/DD/YYYY
  6. cast('04,12,2014' as date) as d5, -- MM,DD,YYYY
  7. cast('04.12.14' as date) as d6, -- DD.MM.YY
  8. -- DD.MM with current year
  9. cast('04.12' as date) as d7,
  10. -- MM/DD with current year
  11. cast('04/12' as date) as d8,
  12. cast('2014/12/04' as date) as d9, -- YYYY/MM/DD
  13. cast('2014 12 04' as date) as d10, -- YYYY MM DD
  14. cast('2014.12.04' as date) as d11, -- YYYY.MM.DD
  15. cast('2014-12-04' as date) as d12, -- YYYY-MM-DD
  16. cast('4 Jan 2014' as date) as d13, -- DD MM YYYY
  17. cast('2014 Jan 4' as date) as dt14, -- YYYY MM DD
  18. cast('Jan 4, 2014' as date) as dt15, -- MM DD, YYYY
  19. cast('11:37' as time) as t1, -- HH:mm
  20. cast('11:37:12' as time) as t2, -- HH:mm:ss
  21. cast('11:31:12.1234' as time) as t3, -- HH:mm:ss.nnnn
  22. cast('11.37.12' as time) as t4, -- HH.mm.ss
  23. -- DD.MM.YYYY HH:mm
  24. cast('04.12.2014 11:37' as timestamp) as dt1,
  25. -- MM/DD/YYYY HH:mm:ss
  26. cast('04/12/2014 11:37:12' as timestamp) as dt2,
  27. -- DD.MM.YYYY HH:mm:ss.nnnn
  28. cast('04.12.2014 11:31:12.1234' as timestamp) as dt3,
  29. -- MM/DD/YYYY HH.mm.ss
  30. cast('04/12/2014 11.37.12' as timestamp) as dt4
  31. from rdb$database
Shorthand Casts for Date and Time Data Types

Firebird allows the use of a shorthand “C-style” type syntax for casts from string to the types DATE, TIME and TIMESTAMP.

Syntax

  1. <data_type> 'date_literal_string'

Example

  1. -- 1
  2. UPDATE PEOPLE
  3. SET AGECAT = 'SENIOR'
  4. WHERE BIRTHDATE < DATE '1-Jan-1943';
  5. -- 2
  6. INSERT INTO APPOINTMENTS
  7. (EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME)
  8. VALUES (973, 8804, DATE 'today' + 2, TIME '16:00');
  9. -- 3
  10. NEW.LASTMOD = TIMESTAMP 'now';

These shorthand expressions are evaluated directly during parsing, as though the statement were already prepared for execution. Thus, even if the query is run several times, the value of, for instance, timestamp ‘now’ remains the same no matter how much time passes.

If you need the time to be evaluated at each execution, use the full CAST syntax. An example of using such an expression in a trigger:

  1. NEW.CHANGE_DATE = CAST(‘now AS TIMESTAMP);

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

  1. UPDATE ATABLE
  2. SET ADATE = '25.12.2016' + 1

and the date literal will be cast to the date type implicitly.

In Dialect 3, this statement will throw error 35544569, “Dynamic SQL Error: expression evaluation not supported, Strings cannot be added or subtracted in dialect 3” — a cast will be needed:

  1. UPDATE ATABLE
  2. SET ADATE = CAST ('25.12.2016' AS DATE) + 1

or, with the short cast:

  1. UPDATE ATABLE
  2. 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,

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

  1. 2 + CAST('1' AS SMALLINT)

The exception to the rule is during string concatenation.

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

  1. SELECT 30||' days hath September, April, June and November' CONCAT$
  2. FROM RDB$DATABASE;
  3. CONCAT$
  4. ------------------------------------------------
  5. 30 days hath September, April, June and November