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.

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;
          

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 3.7) or a representation of the date in one of the allowed date-time literal formats:

       <datetime_literal> ::= {
         [YYYY<p>]MM<p>DD[<p>HH[<p>mm[<p>SS[<p>NNNN]]]] |
         MM<p>DD[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |
         DD<p>MM[<p>YYYY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |
         MM<p>DD[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |
         DD<p>MM[<p>YY[<p>HH[<p>mm[<p>SS[<p>NNNN]]]]] |
         NOW |
         TODAY |
         TOMORROW |
         YESTERDAY
       }

       <date_literal> ::= {
         [YYYY<p>]MM<p>DD |
         MM<p>DD[<p>YYYY] |
         DD<p>MM[<p>YYYY] |
         MM<p>DD[<p>YY] |
         DD<p>MM[<p>YY] |
         TODAY |
         TOMORROW |
         YESTERDAY
       }

       <time_literal> := HH[<p>mm[<p>SS[<p>NNNN]]]

       <p> ::= whitespace | . | : | , | - | /

          

	Important
	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: 

       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,2014' as date) as d5, -- MM,DD,YYYY
         cast('04.12.14' as date) as d6,   -- DD.MM.YY
         -- DD.MM with current year
         cast('04.12' as date) as d7,
         -- MM/DD with current year
         cast('04/12' as date) as d8,
         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('2014-12-04' as date) as d12, -- YYYY-MM-DD
         cast('4 Jan 2014' as date) as d13, -- DD MM YYYY
         cast('2014 Jan 4' as date) as dt14, -- YYYY MM DD
         cast('Jan 4, 2014' as date) as dt15, -- 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
         cast('11.37.12' as time) as t4, -- HH.mm.ss
         -- 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,
         -- MM/DD/YYYY HH.mm.ss
         cast('04/12/2014 11.37.12' as timestamp) as dt4
       from rdb$database
          

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

Syntax: 

       data_type 'date_literal_string'
          

Example: 

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

Note

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: NEW.CHANGE_DATE = CAST('now' AS TIMESTAMP);

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