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)

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.