Problem
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.
Background
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
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!
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
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
Discussion
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.
This isn’t always 30 days, as you see by comparing the two StartDate values.
Leave a Reply