DATE
<< Database trigger | IBExpert Glossary | DBMS (Database Management System) >>
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.
See also:
TIME
TIMESTAMP
Table Editor
SQL Editor
Data types and subtypes
Selecting the right data type to improve database performance
Firebird 2.5 Language Reference Update
Firebird 2.1 Language Reference Update
Firebird 2.0 Language Reference Update
SQL Language Reference
back to top of page
<< Database trigger | IBExpert Glossary | DBMS (Database Management System) >>
