DATE

The DATE data type stores values which represent a date. Firebird/InterBase® supports a single DATE-type column that requires 8 bytes of storage space. It uses 4 bytes for the date and 4 bytes for the time.

Valid dates are from January 1, 100 AD through February 28, 32,767 AD. Note: for DATE arithmetic purposes, DATE 0 (the integer value of zero) as a DATE in Firebird/InterBase® is November 17, 1898.

Different date formats are supported. There are however slight differences between SQL dialect 1 and SQL dialect 3.

• SQL dialect 1: DATE also includes a time slice (equivalent to TIMESTAMP in dialect 3).
• SQL dialect 3: DATE does not include any time slice.

Using SQL dialect 1 the default NOW for data type DATE means current time and date of the server; there is also TODAY (only date; the time is always set at midnight, YESTERDAY, TOMORROW).

Example:

SELECT CAST ("NOW" AS DATE) FROM RDB$DATABASE 

SELECT CAST is an SQL dialect 1 command (although it also functions in SQL dialect 3); SELECT is used in SQL dialect 3. These values are primarily compatible to older InterBase® versions. When working with SQL dialect 3, the CURRENT_ constants (see below) should be used as far as possible.

From InterBase® 6 upwards and Firebird there are the following for dialect 3: CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_DATE (without quotation marks and without CAST). Example:

SELECT CURRENT_DATE-1 FROM RDB$DATABASE 

Result: the date yesterday, etc.

SELECT CURRENT_TIMESTAMP-(1/24) FROM RDB$DATABASE 

Result: the current time minus one hour (one twenty-fourth of a day).

It is possible to specify the display format of a date field under Environment Options / Grid / Display Formats. For the various options available, please refer to Date Time Format.