Contact Us | Site Map
Firebird is used by approximately 1 million of software developers worldwide. High compatibility with industry standards on many fronts makes Firebird the obvious choice for developing interoperable applications for homogeneous and hybrid environments.
Join Firebird!
Join Firebird Foundation to support Firebird SQL development and receive multiple bonuses
Follow Us
Select your media preference
Google+ Twitter Facebook RSS
Newsletter
Subscribe to Firebird’s Newsletter to receive the latest news
Firebird Date Literals
Excerpt from Chapter 10 of The Firebird Book © Helen Borrie 2004

Table 10-1. Elements of Date Literals

Element Representing
CC Century. First two digits of a year segment (e.g., 20 for the twenty-first century).
YY Year in century. Firebird always stores the full year value if the year is entered without the CC segment, using a "sliding window" algorithm (see below) to determine which century to store.
MM Month, evaluating to an integer in the range 1 to 12. In some formats, two digits are required.
MMM Month, one of [JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC]. English month names fully spelled out (correctly) are also valid.
DD Day of the month, evaluating to an integer in the range 1 to 31. In some formats, two digits are required. An invalid day-of-month number for the given month will cause an error.
HH Hours, evaluating to an integer in the range 00 to 23. Two digits are required when storing a time portion.
NN Minutes, evaluating to an integer in the range 00 to 59. Two digits are required when storing a time portion.
SS Whole seconds, evaluating to an integer in the range 00 to 59. Two digits are required when storing a time portion.
nnnn Ten-thousandths of a second in the range 0 to 9999. Optional for time portions; defaults to 0000. If used, four digits are required.

The recognized formats are described in Table 10-2.

Table 10-2. Recognized Date and Time Literal Formats

Format Dialect 3 DATE Dialect 3 TIMESTAMP Dialect 1 DATE
'CCYY-MM-DD' or 'YY-MM-DD' Stores date only Stores the date and a time portion of 00:00:00 Stores the date and a time portion of 00:00:00
'MM/DD/CCYY' or 'MM/DD/YY' As above As above As above
'DD.MM.CCYY' or 'DD.MM.YY' As above As above As above
'DD-MMM-CCYY' or 'DD-MMM-YY' As above As above As above
'DD,MMM,CCYY' or 'DD,MMM,YY' As above As above As above
'DD MMM CCYY' or 'DD MMM YY' As above As above As above
'DDMMMCCYY' or 'DDMMMYY' As above As above As above
Case-insensitive English month names fully spelled out are also valid in the MMM element. Correct spelling is shown in Table 10-3.
'CCYY-MM-DD HH:NN:SS.nnnn' or 'YY-MM-DD HH:NN:SS.nnnn' (".nnnn" element is optional) Stores date only; may need to be CAST as date. Time portion is not stored. Stores date and time  
'MM/DD/CCYYHH:NN:SS.nnnn'or 'MM/DD/YY HH:NN:SS.nnnn' As above As above As above
'DD.MM.CCYYHH:NN:SS.nnnn'or 'DD.MM.YY HH:NN:SS.nnnn' As above As above As above
'DD-MMM-CCYY HH:NN:SS.nnnn' or 'DD-MMM-YY HH:NN:SS.nnnn' As above As above As above

The dialect 3 TIMESTAMP type and the dialect 1 DATE type accept both date and time parts in a date literal. A date literal submitted without a time part will be stored with a time part equivalent to '00:00:00'.

The dialect 3 DATE type accepts only the date part. The TIME data type accepts only the time part.

Firebird's "Sliding Century Window"

Whether the year part of a DATE or TIMESTAMP literal is submitted in SQL as CCYY or YY, Firebird always stores the full year value. It applies an algorithm in order to deduce the CC (century) part, and it always includes the century part when it retrieves date types. Client applications are responsible for displaying the year as two or four digits.

To deduce the century, Firebird uses a sliding window algorithm. Its effect is to interpret a two-digit year value as the nearest year to the current year, in a range spanning the preceding 50 years and the succeeding 50 years.

For example, if the current year were 2004, two-digit year values would be interpreted as shown in Table 10-3.

Table 10-3. Deduction of Year from Two-Digit Year if Current Year is 2004

Two-Digit Year Becomes Year Deduced From
98 1998 (2004 – 1998 = 6) < ( 2098 – 2004 = 94)
00 2000 (2004 – 2000 = 4) < (2100 – 2004 = 96)
45 2045 (2004 – 1945 = 55) > (2045 – 2004 = 41)
50 2050 (2004 – 1950 = 54) > (2050 – 2004 = 46)
54 1954 (2004 – 1954 = 50) = (2054 – 2004 = 50) ‡
55 1955 (2004 – 1955 = 49) < (2055 – 2004 = 51)
‡ The apparent equivalence of this comparison could be misleading. However, 1954 is closer to 2004 than is 2054 because all dates between 1954 and 1955 are closer to 2004 than all dates between 2054 and 2055.

Separators in Non-U.S. Dates

Nothing causes more confusion for international users than Firebird's restricting the use of the forward slash character (/) to only the U.S. 'MM/DD/CCYY' format. Although almost all other countries use 'DD/MM/CCYY', Firebird will either record the wrong date or throw an exception with the date literal using the 'DD/MM/CCYY' convention.

For example, the date literal '12/01/2004' will always be stored with meaning "December 1, 2004" and '14/01/2004' will cause an out-of-range exception because there is no month 14.

Note that Firebird does not honor the Windows or Linux date locale format when interpreting date literals. Its interpretation of all-number date formats is decided by the separator character. When dot (.) is used as separator, Firebird interprets it as the non-U.S. notation DD.MM, whereas with any other separator it assumes the U.S. MM/DD notation. Outside the U.S. date locale, your applications should enforce or convert locale-specific DD/MM/CCYY date input to a literal that replaces the forward slash with a period (dot) as the separator. 'DD.MM.CCYY' is valid. Other date literal formats may be substituted.

White Space in Date Literals

Spaces or tabs can appear between elements. A date part must be separated from a time part by at least one space.

Quoting of Date Literals

Date literals must be enclosed in single quotes (ASCII 39). Only single quotes are valid.

Month Literals

Table 10-4. Month Literals with Correct English Spellings

Cardinal Number Abbreviation Full Month Name
-- Case-Insensitive Case-Insensitive
01 JAN January
02 FEB February
03 MAR March
04 APR April
05 MAY May
06 JUN June
07 JUL July
08 AUG August
09 SEP September
10 OCT October
11 NOV November
12 DEC December

Examples of Date Literals

The twenty-fifth (25) day of the sixth month (June) in the year 2004 can be represented in all of the following ways:

'25.6.2004' '06/25/2004' 'June 25, 2004'

'25.jun.2004' '6,25,2004' '25,jun,2004'

'25jun2004' '6-25-04' 'Jun 25 04'

'25 jun 2004' '2004 June 25' '20040625'

'25-jun-2004' '2004-jun-25' '20040625'

'25 JUN 04' '2004-06-25' '2004,25,06'


Predefined Date Literals

Firebird supports a group of "predefined" date literals—single-quoted English words that Firebird captures or calculates and interprets in the context of an appropriate date/time type. The words 'TODAY', 'NOW', 'YESTERDAY', and 'TOMORROW' are interpreted as shown in Table 10-5.

Table 10-5. Predefined Date Literals

Date Literal Dialect 3 Type Dialect 1 Type Meaning
'NOW' TIMESTAMP DATE Server date and time that was current at the start of the DML operation. 'NOW' will be cast and stored correctly in dialect 3 DATE, TIME, and TIMESTAMP fields and in dialect 1 DATE fields. Like the equivalent context variable CURRENT_TIMESTAMP, it always stores the subsecond portion as '.0000'. †
'TODAY' DATE DATE stored with a time part equivalent to '00:00:00' Server date that was current at the start of the operation. If midnight is passed during the operation, the date does not change. Equivalent to the dialect 3 CURRENT_DATE context variable. Not valid in fields of TIME type.
'TOMORROW' DATE DATE stored with a time part equivalent to '00:00:00' Server date that was current at start of the operation, plus 1 day. If midnight is passed during the operation, the date from which the 'TOMORROW' date is calculated does not change. Not valid in fields of TIME type.
'YESTERDAY' DATE DATE stored with a time part equivalent to '00:00:00' Server date that was current at start of the operation, minus 1 day. If midnight is passed during the operation, the date from which the 'YESTERDAY ' date is calculated does not change. Not valid in fields of TIME type.

Home About Firebird Documentation Downloads Community Support Development Case Studies Back to Top
This site and the pages contained within are Copyright © 2000-2014, Firebird Project. Firebird® is a registered trademark of Firebird Foundation Incorporated. Developed by DQ Team.