Back to cookbooks list Articles Cookbook

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



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


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

1Sony GX10002019-01-20
2Samsung LX20002019-04-15
3Dell K802019-08-30

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


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

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

Here’s the result of the query:

Sony GX10002018-12-21
Samsung LX20002019-03-16
Dell K802019-07-31


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.

Recommended courses:

Recommended articles:

See also: