Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

TIMESTAMPADD function (ODBC compatible)

Syntax

TIMESTAMPADD(interval, integer_exp, date_time_exp)

interval::

SQL_TSI_FRAC_SECOND

| SQL_TSI_SECOND

| SQL_TSI_MINUTE

| SQL_TSI_HOUR

| SQL_TSI_DAY

| SQL_TSI_DAYOFYEAR

| SQL_TSI_WEEK

| SQL_TSI_MONTH

| SQL_TSI_QUARTER

| SQL_TSI_YEAR

| MILLISECOND

| SECOND

| MINUTE

| HOUR

| DAY

| DAYOFYEAR

| WEEK

| MONTH

| QUARTER

| YEAR

Description

Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.

Arguments

  • interval - Keywords that specify the interval to add to timestamp_exp. The SQL_TSI_FRAC_SECOND keyword specifies fractional seconds as milliseconds.

    Keywords such as SECOND and HOUR are functionally equivalent to the keywords with the same name but having prefix SQL_TSI, i.e., the keywords such as SQL_TSI_SECOND and SQL_TSI_HOUR. The keyword MILLISECOND is the same as SQL_TSI_FRAC_SECOND and this interval means milliseconds. The keyword DAYOFYEAR or SQL_TSI_DAYOFYEAR indicates an interval value which ranges from 1 to 366 (See scalar function DAYOFYEAR()).

  • integer_exp - The number of interval values to add to timestamp_exp. integer_exp can be any expression that evaluates to an integer data type.
  • date_time_exp - A date-time expression from which TIMESTAMPADD calculates the return value. If date_time_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

Example

The following example displays the current system time and uses the TIMESTAMPADD scalar function to add 8 hours to it.

> select systime, timestampadd(sql_tsi_hour, 8, systime) from syscalctable;

15:03:57:000 06/08/1999 23:03:57:000

------------ -----------------------

15:03:57:000, 1999-06-08 23:03:57:000

TOCIndex