How to Subtract 30 Days from a Date in T-SQL

Problem:

You’d like to get the date 30 days before a given date in T-SQL.

Example:

Our database has a table named Computer with data in the columns Id, Name, and PurchaseDate.

Let’s get the name of each computer and the date 30 days before its purchase date.

Solution:

We will use the DATEADD() function to subtract a given number of days from a date.

	SELECT Name,
		  DATEADD(day, -30, PurchaseDate)
		    AS BeforePurchaseDate;
      FROM Computer;

Here’s the result of the query:

Discussion:

If you would like to subtract dates or times in SQL Server, use the DATEADD() function. It takes three arguments. The first argument is the date/time unit – in our example, we specify the day unit.

Next is the date or time unit value. In our example, this is -30, because we’re taking 30 days away from the current date. Remember that the minus denotes subtracting the value; without this sign, you’re adding to the given date.

The last argument is the date we’re operating on; this could be a date/time/datetime column or any expression that returns a date or time. In our example, we use PurchaseDate, a date column.

The function returns a changed date. In our example, the query for the computer named 'Dell K80' returns a new date in the BeforePurchaseDate column. The original date '2019-08-30' is changed to the date from 30 days back: '2018-07-31'.

You can use the DATEADD() function for all date and time data types.