Skip to Content

DateAdd() mvSQL database function in Jitterbit App Builder

Summary

Adds or subtracts a given quantity of the specified date part to a date or datetime expression.

Syntax

DATEADD(<datepart>, <quantity>, <datetime>)

Parameters

datepart

Date Part Aliases Vendor Support
year yy, yyyy All RDBMS vendors.
quarter qq, q SQL Server and SQLite
month mm, m All RDBMS vendors.
dayofyear dy, y SQL Server and SQLite
day dd, d All RDBMS vendors.
week wk, ww SQL Server and SQLite
weekday dw, w SQL Server and SQLite
hour hh All RDBMS vendors.
minute mi, n All RDBMS vendors.
second ss, s All RDBMS vendors.

The date part must not be quoted.

The date part is not case-sensitive.

quantity

Column or expression of type int.

datetime

Column or expression of type datetime or date. Some date parts expect a datetime.

Returns

Returns a datetime or date.

Remarks

Each database vendor may handle date boundaries differently. For example, SQL Server calculates weeks using Sunday as the start of the week. SQLite, on the other hand, uses Monday.

When adding months to a date, SQLite will "normalize" the date. Adding 1 month to 2001-03-31 results in 2001-05-01. In SQL Server, adding 1 month to 2001-03-31 returns 2001-04-30. This also affects quarters. SQLite doesn't natively support quarters. App Builder polyfills the quarter date part using 3 month intervals.

Examples

Note that these examples assume SQL Server.

Expression Returns
DATEADD(year, 1, TIMESTAMP('2005-12-31 23:59:59.9999999')) 2006-12-31 23:59:59.9999999
DATEADD(month, 1, TIMESTAMP('2005-12-31 23:59:59.9999999')) 2006-01-31 23:59:59.9999999