Using dateadd in SQL Server to add intervals to datesPosted in Microsoft SQL Server -
Last week I posted how to use date_add() in MySQL to add intervals to dates and today I will do the same but for Microsoft SQL Server using the dateadd() function.
The syntax for the dateadd() function in SQL Server is as follows:
DATEADD (datepart, number, date)
"datepart" is the interval type you wish to add or subtract for example day, month, year, hour, minute, second. These can be abbreviated as dd and d for day, mm and m for month, yy and yyyy for year, hh for hour, mi and n for minute and ss and s for second.
The number is the amount of datepart units to add or subtract. For example if datepart was d and the number was 3 then it would add three days to the date specified.
And finally, date is the date to add/subtract the number of dateparts from. It can either be a column in the database, a constant value or a function such as GETDATE() which would return the current date and time.
Using the same examples from the MySQL post, to show the current date and time, and to add a month to the current date you would do this:
SELECT GETDATE(), DATEADD(month, 1, GETDATE())
which would give you the following result:
2008-10-03 14:57:09.907 2008-11-03 14:57:09.907
To subtract a month to the current, simply make a negative number value:
SELECT GETDATE(), DATEADD(month, -1, GETDATE())
which would give the following result:
2008-10-03 14:58:08.113 2008-09-03 14:58:08.113
And to use the same example from the MySQL article, if we have a table called "products" and it has a column called "backorder_date" which has a column type of date, we could run this query to add three days onto the back order date which is the value we might display on a website:
SELECT DATEADD(day, 3, backorder_date) AS backorder_date FROM products
and an example result
It's very easy to add and subtract dates using Microsoft SQL Server. The are often circumstances when you would need to use this and do it in the database rather than in business logic or website code, such as calculating the backorder date of a product in the last example above.