Recognized Date/Time Literal Formats - Firebird

The formats of the strings recognized as date literals are restricted. These formats use placeholders for the elements of the strings. Table provides a key to the conventions used.

Elements of Date Literals

Elements of Date Literals

The recognized formats are described in Table.

Recognized Date and Time Literal Formats

Recognized Date and Time Literal Formats

Recognized Date and Time Literal Formats

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.

Deduction of Year from Two-Digit Year if Current Year is 2004

Deduction of Year from Two-Digit Year if Current Year is 2004

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 shows the month literals.

Month Literals with Correct English Spellings

Month Literals with Correct English Spellings

Month Literals with Correct English Spellings

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:


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics