DATE data type

The DATE data type stores the calendar date. DATE data types require four bytes. A calendar date is stored internally as an integer value equal to the number of days since December 31, 1899.

Because DATE values are stored as integers, you can use them in arithmetic expressions. For example, you can subtract a DATE value from another DATE value. The result, a positive or negative INTEGER value, indicates the number of days that elapsed between the two dates. (You can use a UNITS DAY expression to convert the result to an INTERVAL DAY TO DAY data type.)

The following example shows the default display format of a DATE column:

mm/dd/yyyy

In this example, mm is the month (1-12), dd is the day of the month (1-31), and yyyy is the year (0001-9999). You can specify a different order of time units and a different time-unit separator than / (or no separator) by setting the DBDATE environment variable. For more information, see DBDATE environment variable.

In non-default locales, you can display dates in culture-specific formats. The locale and the GL_DATE and DBDATE environment variables (as described in the next chapter) affect the display formatting of DATE values. They do not, however, affect the internal storage format for DATE columns in the database. For more information, see the HCL OneDB™ GLS User's Guide.