DATEADD

Syntax

DATEADD (interval, number, date)

Description

Returns a date to which a specified amount of time has been added.

Arguments

interval	A String representing a unit of time that can be: — yyyy = years — q = quarters — m = months — d = days — ww = weeks — h = hours — n = minutes — s = seconds
number date	Number of [years/months/days/etc.] to add A date and time as a DateTime or String

Returns

A DateTime date that is earlier or later than the original date.

The DateTime data type allows you to store and manipulate date and time data.

When appropriate, such as for the GETDATETIME function, the DateTime format is based on the local date time setting of the computer where the agent is installed.

Remarks

number can be positive, to get dates in the future, or negative, to get dates in the past.

DATEADD does not change the value of the source DateTime. Instead, a new DateTime is returned whose value is the result of this operation.

DATEADD ("h", -5, MyEntity.Timestamp)

Returns: A datetime value which is 5 hours less than the contents of the Timestamp field.

Many datastores to which TIBCO Cloud™ Integration - Connect connects explicitly define time zones when sending or receiving time-based data to and from TIBCO Cloud™ Integration - Connect. In these cases, TIBCO Cloud™ Integration - Connect honors time zone information and properly handles time-based information.

However, some applications do not define the time zone, such as the Microsoft SQL Server DATETIME and DATETIME2 data types. In such cases, TIBCO Cloud™ Integration - Connect assumes that all time-based information is in UTC (GMT) time. While many database-backed applications do store time-based information in UTC, some applications store this information in a different time zone, often the server’s local time. In this case, to ensure that the integration works correctly, you must convert any time-based data from the source data store to UTC before updating or inserting into the target.

For example, assume you have a source database stored on a server in Boston that stores time-based information in the local time zone, US Eastern, which is UTC -5:00, while the target data store expects time data in UTC. If TIBCO Cloud™ Integration - Connect assumes the source system is UTC, timestamps and other data are shifted by 5 hours when sent to the target system.

Examples

The following example adds 3 months to MyEntity.PurchaseDate. If the value of the Purchase.Date field is 12-1-2012:

(DATEADD ("m", 3, MyEntity.PurchaseDate))

Returns: 2-1-2013

The following example subtracts 5 years from the current DateTime. If the current date and time is 12/02/2014 11:27:23 AM:

DATEADD ("yyyy", -5, GETDATETIME ( ))

Returns: 12/02/2009 11:27:23 AM