Date and Time Data Types
Go Up to Specifying Data Types
TIMESTAMP data types.
DATEstores a date as a 32-bit longword. Valid dates are from January 1, 100 a.d. to February 29, 32768 a.d.
TIMEstores time as a 32-bit longword. Valid times are from 00:00 AM to 23:59.9999 PM.
TIMESTAMPis stored as two 32-bit longwords and is a combination of
The following statement creates
TIMESTAMP columns in the
CREATE TABLE SALES (. . . ORDER_DATE TIMESTAMP DEFAULT 'now' NOT NULL, SHIP_DATE TIMESTAMP CHECK (SHIP_DATE >= ORDER_DATE OR SHIP_DATE IS NULL), . . .);
In the previous example,
NOW returns the system date and time.
Converting to the DATE, TIME, and TIMESTAMP Data Types
Most languages do not support the
TIMESTAMP data types. Instead, they express them as strings or structures. These data types requires conversion to and from InterBase when entered or manipulated in a host-language program. For example, you could convert to the
DATE data type in one of the following ways:
- Create a string in a format that InterBase understands (for example, 1-JAN-1999). When you insert the date into a
DATEcolumn, InterBase automatically converts the text into the internal
- Use the call interface routines provided by InterBase to do the conversion.
isc_decode_date()converts from the InterBase internal
DATEformat to the C time structure.
isc_encode_date()converts from the C time structure to the internal InterBase
- Note: The string conversion described in the first bullet does not work in the other direction. To read a date in an InterBase format and convert it to a C date variable, you must call
For more information about how to convert
TIMESTAMP data types in C, and how to use the
CAST() function for type conversion using
SELECT statements, refer to “Using CAST( ) to convert dates and times” in “Working with Dates and Times” in the Embedded SQL Guide.
How InterBase Stores Date Values
InterBase stores all date values correctly, including those after the year 2000. InterBase always stores the full year value in a
TIMESTAMP column, never the two-digit abbreviated value. When a client application enters a two-digit year value, InterBase uses the “sliding window” algorithm, described below, to make an inference about the century and stores the full date value including the century. When you retrieve the data, InterBase returns the full year value including the century information. It is up to client applications to display the information with two or four digits.
InterBase uses the following sliding window algorithm to infer a century:
- Compare the two-digit year number entered to the current year modulo 100.
- If the absolute difference is greater than 50, then infer that the century of the number entered is 20, otherwise it is 19.
For a more detailed explanation of the InterBase algorithm and how it is applied, see the “Working with Dates and Times” chapter in the Embedded SQL Guide.