Date and Time Data Types

Go Up to Specifying Data Types

InterBase supports DATE,TIME and TIMESTAMP data types.

• DATE stores a date as a 32-bit longword. Valid dates are from January 1, 100 a.d. to February 29, 32768 a.d.
• TIME stores time as a 32-bit longword. Valid times are from 00:00 AM to 23:59.9999 PM.
• TIMESTAMP is stored as two 32-bit longwords and is a combination of DATE and TIME.

The following statement creates TIMESTAMP columns in the SALES table:

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 DATE,TIME and 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 DATE column, InterBase automatically converts the text into the internal DATE format.
• Use the call interface routines provided by InterBase to do the conversion. isc_decode_date() converts from the InterBase internal DATE format to the C time structure. isc_encode_date() converts from the C time structure to the internal InterBase DATE format.

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 isc_decode_date().

For more information about how to convert DATE,TIME and 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 DATE or 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.

Advance To:

• Character Data Types