Using CAST(..) with Date/Time Types - Firebird

At several points in this chapter, we have encountered the CAST(..) function in expressions involving date literals and date parts. This section explores the various aspects of date and time casting in more depth and breadth.

Casting Between Date/Time Types

Generally, casting from one date/time type to another is possible wherever the source date/time type provides the right kind of data to reconstruct as the destination date/ time type. For example, a TIMESTAMP can provide the date part to cast on to a date only DATE type or a time-only TIME type, whereas a TIME type cannot provide enough data to cast on to a DATE type. Firebird allows a DATE type to be cast to TIMESTAMP by casting on a time part of midnight, and it allows a TIME type to be cast to TIMESTAMP by casting it on to CURRENT_DATE (the server date). Table summarizes the casting rules.

Dialect 3 Casting Between Date/Time Types

Dialect 3 Casting Between Date/Time Types

Casting from Date Types to CHAR(n) and VARCHAR(n)

Use the SQL CAST() function in statements to translate between date and time data types and character-based data types.

Firebird casts date/time types to formatted strings where the date (if present) is in a set format—dependent on dialect—and the time part (if present) is in the standard Firebird HH:NN:SS.nnnn time format. It is necessary to prepare a CHAR or VARCHAR column or variable of a suitable size to accommodate the output you want.

Both fixed length CHAR and variable-length VARCHAR types can be cast to from date/time types. Because the size of a cast date/time string is known and predictable, CHAR has a slight advantage over VARCHAR where date and time castings are concerned: Using CHAR will save you transmitting over the wire the 2 bytes that are added to VARCHARs to store their length. The “right size” depends on dialect, so care is needed here. VARCHAR may be more suitable to use in application code that may need to handle both dialects.

If the character field is too small for the output, an overflow exception will occur. Suppose you want a cast string to contain only the date part of a TIMESTAMP. Preparing a character container of smaller size will not work: CAST(..) does not truncate the output string to fit. It is necessary to perform a double cast, first casting the timestamp as DATE and then casting that date to the correctly sized character type—refer to the examples in the following sections.

Dialect 3

Casting DATE or TIMESTAMP outputs the date part in ISO format (CCYY-MM-DD). To get the full length of the output, allow 10 characters for DATE and 11 for TIMESTAMP (the date part plus 1 for the blank preceding the time part). Allow 13 characters for TIME or the time part of TIMESTAMP. For example, the following:

SELECT CAST(timestamp_col as CHAR(24)) AS TstampTxt FROM RDB$DATABASE ;

produces a string like this:

2004-06-25 12:15:45.2345

This produces an overflow exception:

SELECT CAST(timestamp_col as CHAR(20)) AS TstampTxt FROM RDB$DATABASE ;

A double-cast will produce the right string:

SELECT FIRST 1 CAST ( CAST (timestamp_col AS DATE) AS CHAR(10)) FROM table1;

The result is


Unfortunately, it is not possible by direct casting to return a cast date plus time string without the sub-second portion. It can be done using a complex expression involving both CAST(..) and EXTRACT( ). For an example, refer to the upcoming section “The EXTRACT( ) Function.”

Dialect 1

The date part of a dialect 1 DATE type is converted to the format DD-MMM-CCYY, not the ISO format as in dialect 3. So, for example, this:

SELECT CAST(d1date_col as CHAR(25)) AS DateTimeTxt FROM RDB$DATABASE;


26-JUN-2004 12:15:45.2345

Consequently, casting dialect 1 dates requires 11 characters instead of 10 for the date part, plus 1 for the blank space, plus 13 for the time part—25 in all.

More Complex Expressions

Casting can be used more complex expressions, in combination with other expression operators, for example:

select cast (10 + cast(('today') as date) as char(25)) texttime from rdb$database;


select cast (10 + current_timestamp) as date) as char(25)) texttime from rdb$database;

produces a text string showing a date 10 days advanced from today’s date.

Casting Between Date/Time Types and Other Types

Any character type or expression whose content can be evaluated to a valid date literal can be cast to the appropriate date/time type.

Date and time types cannot be cast to or from SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC, DECIMAL, or BLOB types.

Uses for Casting

Exchanging Date/Time Data with Other Applications

Importing date/time data created elsewhere—by another database system, host language, or data-capture device, for example—usually involves some degree of “mas- saging” before it can become valid date/time data for storage in a Firebird database.

Most host languages do not support the DATE, TIME, and TIMESTAMP types, representing them internally as strings or structures. Data capture devices usually store dates and times in a variety of string formats and styles. Date/time types are often incompatible between different database hosts.

Conversion generally requires evaluating and decoding the date-element content of the source data. The second part of the process is to reconstruct the decoded elements and pass them in Firebird SQL by some means. For a host language that has no means to pass native Firebird date/time types, use of CAST(..) in combination with valid text strings for Firebird to process as date literals can be invaluable.

In some cases, external data stored into text files in date literal formats may be the best solution. Firebird can open such files as input tables in a server-side code module—stored procedure or trigger—and use CAST(..) and other functions to process data into date/time columns in native tables.

CAST(..) can equally be used to prepare internal data for export.

In Search Condition Expressions

Situations arise where using CAST(..) in the WHERE clause with a date/time type will solve logical problems inherent in comparing a column of one type with a column of a different type.

Suppose, for example, we want to join a customer account table, which contains a DATE column BALANCE_DATE, with a customer transaction table, which has a TIME- STAMP column TRANS_DATE. We want to make a WHERE clause that returns a set containing all of the unbilled transactions for this customer that occurred on or before the BALANCE_DATE. We might try this:


This criterion does not give us what we want! It will find all of the transaction rows up to midnight of the BALANCE_DATE, because it evaluates BALANCE_DATE with a time part of 00:00:00. Any transactions after midnight on that date will fail the search criterion.

What we really want is to include all of the transactions where the date part of TRANS_DATE matches BALANCE_DATE. Casting TRANS_DATE to a DATE type saves the day:


In a Dialect Conversion

Dialect 3 provides richer date/time support than dialect 1. One task that is likely to catch your attention if you do such a conversion is to replace or enhance existing dialect 1 DATE type columns (which are equivalent to TIMESTAMP in dialect 3) by converting them to the dialect 3 DATE (date-only) or TIME types. CAST(..) makes this job a no brainer.

For an example of one style of conversion using CAST( ), refer to the sample at the end of this chapter.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics