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