Date tricks in Firebird

Stolen from: http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html

Tricks for Firebird dates.


A Useful Trick with Date Literals
H. Borrie

In days gone by, before the advent of context variables like CURRENT_DATE, CURRENT_TIMESTAMP, et al., we had predefined date literals, such as 'NOW', 'TODAY', 'YESTERDAY' and so on. These predefined date literals survive in Firebird's SQL language set and are still useful.

In InterBase 5.x and lower, the following statement was “legal” and returned a DATE value ( remembering that the DATE type then was what is now TIMESTAMP):

select 'NOW' from rdb$database /* returns system date and time */


In a database of ODS 10 or higher, that statement returns the string 'NOW'. We have had to learn to cast the date literal to get the result we want:

select cast('NOW' as TIMESTAMP) from rdb$database


For a long time—probably since IB 6— there has been an undocumented “short expression syntax” for casting not just the predefined date/time literals but any date literals. Actually, it is defined in the standard. Most of us were just not aware that it was available. It takes the form . Taking the CAST example above, the short syntax would be as follows:

select TIMESTAMP 'NOW'


This short syntax can participate in other expressions. The following example illustrates a date/time arithmetic operation on a predefined literal:

update mytable
set OVERDUE = 'T'
where DATE 'YESTERDAY' - DATE_DUE > 10