How to Subtract 30 days from current date in SQL?

Question

I am trying to subtract 30 days from a date column in my database and use that as a condition in my where but I can't get it to work table example:

Fact_day
2022-05-20
2022-05-20
2022-04-15
2022-05-28

My trial:

where pr.fact_day between current_date  and current_date - 30

Expected output is to get me all info in the rows that are 30 days before today's date

Please don't spam the tags. Pick the appropriate database – John Cappelletti Jun 25 '22 at 18:18 — John Cappelletti, Jun 25 '22 at 18:18

score 0 · Answer 1 · answered Jun 25 '22 at 18:22

You can use DATEADD to subtract days to a given date

SQL Server DATEADD() Function

DATEADD(interval, number, date)

number Required. The number of interval to add to date. Can be positive (to get dates in the future) or negative (to get dates in the past)

Example:

SELECT DATEADD(day, -30, '2017/08/25') AS DateAdd;

https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

score 0 · Answer 2 · answered Jun 25 '22 at 18:30

Assuming Postgres, from Comparison operators:

The BETWEEN predicate simplifies range tests:

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Notice that BETWEEN treats the endpoint values as included in the range. BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that the argument to the left of AND be less than or equal to the argument on the right. If it is not, those two arguments are automatically swapped, so that a nonempty range is always implied.

So this:

between current_date and current_date - 30

needs to be this:

between current_date - 30 and current_date

unless you use SYMMETRIC.

How to Subtract 30 days from current date in SQL?

2 Answers2