mvSQL Database Function - DATEADD()¶

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. Vinyl 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`