Using dateadd in SQL Server to add intervals to datesUsing dateadd in SQL Server to add intervals to dates

Posted October 4th, 2008 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

2008-10-18 00:00:00.000

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.

Recent posts: