Excerpt from Chapter 10 of The Firebird Book © Helen Borrie 2004
Table 10-1. Elements of Date Literals
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
'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
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
-- |
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
'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. |