SQL Reference Guide

Date-Time Data Types

Refer to Numeric Literals for details on specifying values to be stored in date-time columns. See Date Format Strings for details on date strings and Time Format Strings for details on using format strings to specify the output format of date-time columns.

Syntax

date_time_data_type ::

DATE

| TIME

| TIMESTAMP

Arguments

• DATE

  Type DATE stores a date value as three parts: year, month, and day. The range for the parts is:

  • Year: 1700 to 9999 (in the specific case of 1700 the actual minimum date is 03/01/1700 -- 1 March 1700)
  • Month: 1 to 12
  • Day: Lower limit is 1. The upper limit is determined by the upper limit of the month.

  The c-tree date field stores date as an unsigned 4 bytes integer value representing the number of days since 02/28/1700. Hence the first valid date is 03/01/1700.

  A check is done to limit the date range lower bound to 03/01/1700 in case of a c-tree Plus date type and 01/01/1900 for the FairCom DB SQL TIMESTAMP type.

• TIME

  Type TIME stores a time value as three parts: hours, minutes, and seconds. The range for the parts is:

  • Hours: 0 to 23
  • Minutes: 0 to 59
  • Seconds: 0 to 59
• TIMESTAMP

  Type TIMESTAMP combines the parts of DATE and TIME.

  • Year: 1900 to 9999
  • Month: 1 to 12
  • Day: Lower limit is 1. The upper limit is determined by the upper limit of the month.
  • Hours: 0 to 23
  • Minutes: 0 to 59
  • Seconds: 0 to 59