Auto Update Date for Given range in SQL

Question

I have a SQL query by which I have to extract data from the server but every time I have to change the date range

where date(usf.created_date) BETWEEN '2022-04-01 00:00:00' and '2022-04-30 23:59:59'

but I want to get this range auto-update from today 30 days back. I have tried the casting method Cast(GETDATE() as smalldatetime) but it shows an error in the same.

Answer 1

Assuming based on GETDATE() that this is SQL Server, you either want:

-- get everything going back exactly 30 days from this moment
WHERE usf.created_date >= DATEADD(DAY, -30, GETDATE());

Or:

-- get everything going back 30 days from this morning at midnight
WHERE usf.created_date >= DATEADD(DAY, -30, CONVERT(date, GETDATE()));