Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

Date Arithmetic Expressions

Description

Date arithmetic expressions compute the difference between date-time expressions in terms of days or milliseconds. FairCom DB SQL supports these forms of date arithmetic:

  • Addition and subtraction of integers to and from date-time expressions
  • Subtraction of a date-time expression from another

Syntax

date_arith_expr ::

date_time_expr { + | - } int_expr

| date_time_expr - date_time_expr

Arguments

date_time_expr

An expression that returns a value of type DATE or TIME or TIMESTAMP. A single date-time expression cannot mix data types. All elements of the expression must be the same data type.

Date-time expressions can contain date-time literals, but they must be converted to DATE or TIME using the CAST, CONVERT, or TO_DATE functions (see the following examples as well as CAST function (SQL-92 compatible) and CONVERT function (extension)).

int_expr

An expression that returns an integer value. FairCom DB SQL interprets the integer differently depending on the data type of the date-time expression:

  • For DATE expressions, integers represent days
  • For TIME expressions, integers represent milliseconds
  • For TIMESTAMP expressions, integers represent milliseconds

Examples

The following example manipulates DATE values using date arithmetic. FairCom DB SQL interprets integers as days and returns date differences in units of days:

SELECT C1, C2, C1-C2 FROM DTEST

c1 c2 c1-c2

1956-05-07 1952-09-29 1316

select sysdate,

sysdate - 3 ,

sysdate - cast ('9/29/52' as date)

from dtest;

sysdate sysdate-3 sysdate-convert(date,9/29/52)

1995-03-24 1995-03-21 15516

The following example manipulates TIME values using date arithmetic. FairCom DB SQL interprets integers as milliseconds and returns time differences in milliseconds:

select systime,

systime - 3000,

systime - cast ('15:28:01' as time)

from dtest;

systime systime-3000 systime-convert(time,15:28:01)

15:28:09 15:28:06 8000

TOCIndex