SQL, how to get data to show previous 30 days-CodePudding

First thread, I couldn't find working solutions on others threads.

I'm need to get data from the last 30 days (example 20/09/21 to 20/10/21)

So far my code is:

SELECT * 
FROM my_table
where CURDATE(my_date_column) - INTERVAL 1 MONTH

I also tried

SELECT * 
FROM my_table
WHERE my_date_column >= DATEADD(day,-30, getdate()) 
and   my_date_column <= getdate()

Can anyone help?

CodePudding user response：

try this code (PG example)

SELECT * FROM my_table where my_date_column > ((now() - interval '1 month'))

if you need text to date conversion use this code

SELECT * FROM my_table where my_date_column::date > ((now() - interval '1 month'))

you are using MySQL?

SELECT * 
FROM my_table
where my_date_column > (DATE_SUB(CURDATE(),INTERVAL 1 MONTH))

CodePudding user response：

Give a try with this,

SELECT * FROM MY_TABLE WHERE MY_DATE_COLUMN BETWEEN CONVERT(DATE,DATEADD(DAY,-30,GETDATE())) AND CONVERT(DATE, GETDATE() )

Converting to Date format, so that time can be ignored.

CodePudding user response：

CURRENT_DATE is a standard SQL term which can be used for calculations. So this should do it:

SELECT *
  FROM my_table
 WHERE my_date_column > CURRENT_DATE - 1 MONTH

or in days:

SELECT *
  FROM my_table
 WHERE my_date_column > CURRENT_DATE - 30 DAYS