Date, time, and timestamp data types

 

Date, time, and timestamp are data types that are represented in an internal form not seen by an SQL user.

Date, time, and timestamp can be represented by character string values and assigned to character string variables. The database manager recognizes the following as date, time, and timestamp values:

• A value returned by the DATE, TIME, or TIMESTAMP scalar function.

• A value returned by the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register.

• A value of a character string in the ANSI/ISO standard date, time, or timestamp format, for example, DATE '1950-01-01'.

• A character string when it is an operand of an arithmetic expression or a comparison and the other operand is a date, time, or timestamp. For example, in the predicate:

  … WHERE HIREDATE < '1950-01-01'

  if HIREDATE is a date column, the character string '1950-01-01' is interpreted as a date.

• A character string variable or constant used to set a date, time, or timestamp column in either the SET clause of an UPDATE statement, or the VALUES clause of an INSERT statement.

• Specifying current date and time values
  You can specify a current date, time, or timestamp in an expression by using one of these special registers: CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP.

• Date/time arithmetic
  Addition and subtraction are the only arithmetic operators applicable to date, time, and timestamp values.

 

Parent topic:

Retrieving data using the SELECT statement

 

Related reference

Data types