DATE

SQL Language StructureCommon Language Elements

`DATE`

Syntax

DATE

The DATE data type in Dialect 3 stores only date without time.The available range for storing data is from January 01, 1 to December 31, 9999.

In Dialect 1, DATE is an alias for [fblangref50-datatypes-timestamp].Dialect 1 has no “date-only” type.

Tip

In Dialect 1, date literals without a time part, as well as casts of date mnemonics 'TODAY', 'YESTERDAY' and 'TOMORROW' automatically get a zero time part.

If you need to store a Dialect 1 timestamp literal with an explicit zero time-part, the engine will accept a literal like '2016-12-25 00:00:00.0000'.However, '2016-12-25' would have the same effect, with fewer keystrokes!

Storage of Dates

Internally, Firebird stores dates in a 32-bit integer as a Modified Julian Date, or the number of days since 1858-11-17.An additional restriction is imposed, limiting valid dates to the range from 0001-01-01 AD to 9999-12-31 AD.

`TIME`

Syntax

TIME [{ WITHOUT | WITH } TIME ZONE]

For a bare TIME, WITHOUT TIME ZONE is assumed.

The TIME data type is available in Dialect 3 only.It stores the time of day within the range from 00:00:00.0000 to 23:59:59.9999.

If you need to get the time-part from DATE in Dialect 1, you can use the EXTRACT function.

Examples Using EXTRACT()

EXTRACT (HOUR FROM DATE_FIELD)
EXTRACT (MINUTE FROM DATE_FIELD)
EXTRACT (SECOND FROM DATE_FIELD)

See also the EXTRACT() function in the chapter entitled Built-in Scalar Functions.