How to Get a Date 30 Days into the Future.
Problem
You want to calculate a date 30 days into the Future using SQL. We’ll use the AdventureWorks Production.Product table for our example:
select ProductID, Name, SellStartDate from Production.Product where productID in(1, 770, 927)
Let’s add thirty days to select Product SellStartDates.
Solution
Use the SQLSERVER DATEADD() function to add 30 days to today’s date.
select ProductID, name, SellStartDate, dateadd(day,30,SellStartDate) SellStart30Days from Production.Product where productID in(1, 770, 927)
Here is the result. Notice SellStart30Day is not the end of the month, but 30 days in the future.
Discussion
You can use DATEADD to add days, weeks, months or years dates. The function takes three parameters.
This first parameter is datepart. In our example we specify day.
Use the second parameter to specify the number the number of periods to add, which in our example is 30.
The last parameter is the date we’re adding days to. In our case it is the Product.SelStartDate column.
You can also use DATEADD() to subtract days. To subtract day 30 days, use -30 as the second parameter.
dateadd(day,-30,SellStartDate)
In our example we used DATEADD in the SELECT statement, but keep in mind you can use it anywhere an expression is allowed. I find DATEADD useful when doing comparisons on the WHERE clause.
Leave a Reply