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 |