How to Subtract 30 Days from a Date using SQL




Given a date, you want to subtract 30 days in the past using SQL. Said another way, get the date thirty days in the past.

We’ll show you to do this using MySQL, PostgreSQL, and SQL Server.  Each uses a different approach, so be sure to read each solution to gain an appreciation of each DBMS.


Coupons are used in the Sample PizzaDB and within a window thirty day prior their expiration date.  Given this, Lou, the Pizza shop owner, would like to see when the coupons were in effect. We can find this out by subtracting 30 days from the ExpirationDate.

Our example is based on the sample PizzaDB, you can get it here.

Here is a listing of coupons:

Let’s now see how to use the ExpirationDate to calculate a data 30 day’s prior.


Solution 1 – Use SQL Server DATEADD() to Subtract 30 Days

In SQL Server you can use the DATEADD() function to “subtract” 30 days from the Expiration Date.  Here’s the query to use:

select CouponID, CouponName, CouponDescription, PercentDiscount, ExpirationDate,
   dateadd(d,-30,ExpirationDate) StartDate
from Coupon

To subtract 30 days, we add negative 30 days; tricky.

Here is the result if you’re curious!

Subtract 30 days from a date using DATEADD() function.
Thirty Days in the Past Using SQL Server DATEADD()

Solution 2 – MySQL DATE_ADD()

In MySQL you can use the DATE_ADD() function to “subtract 30 days” from the Expiration Date.  This query is close to how it is done is SQL Server but notice MySQL using the INTERVAL concept.

select CouponID, CouponName, CouponDescription, PercentDiscount, ExpirationDate,
   date_add(ExpirationDate, interval -30 day) StartDate
from Coupon

The value returned is a date and you get the same results are you would with solution 1.

Solution 3 – PostgreSQL Addition with ::INTERVAL

Rather than having a dedicated function to add intervals to dates, PostgreSQL takes the novel approach of performing this operation using operators.

Here is the query to show the StartDate 30 days back from the coupon ExpirationDate:

select CouponID, CouponName, CouponDescription, PercentDiscount, ExpirationDate,
   ExpirationDate + '-30 day'::INTERVAL StartDate
from Coupon
Subtract 30 days from a date using PostgreSQL INTERVAL type
Thirty Days in the Past Using by subtracting a 30 Day INTERVAL in PostgreSQL

This returns a date time type, so to get just a date result, I recommend using this query to represent the ExpirationDate as a Date type:

select CouponID, CouponName, CouponDescription, PercentDiscount, ExpirationDate,
   cast(ExpirationDate + '-30 day'::INTERVAL as date) StartDate
from Coupon


Rather than using than subtracting 30 days, you could subtract months, but understand the number of days you’ll go back is different if you use month.  To see what I mean, look at this query which shows the StartDate calculated as 30 days prior and one month prior:

select CouponID, CouponName, CouponDescription, PercentDiscount, ExpirationDate,
   DateAdd(d,-30,ExpirationDate) StartDate30DaysAgo,
   DateAdd(month,-1,ExpirationDate) StartDate1MonthAgo
from Coupon

Notice that the StartDate1MonthAgo is moving back to the same calendar date within the prior month.

Using Month versus Day interval to subtract dates in SQL
Past Dates: Subtracting 30 Days versus 1 Month

This isn’t always 30 days, as you see by comparing the two StartDate values.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SqlServer