FirebirdSQL logo
 SQL Language StructureCommon Language Elements 

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 [fblangref50-dtyp-tbl-datetimemnemonics]) or a representation of the date in one of the allowed datetime formats (see Datetime Format Syntax),

Table 1. Predefined Datetime Mnemonics

Literal

Description

'NOW'

Current date and time

'TODAY'

Current date

'TOMORROW'

Current date + 1 (day)

'YESTERDAY'

Current date - 1 (day)
Note

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

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.

Note

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);