Can I search between two dates in SQL using DATE

Here is what I have working:

select  date_trunc('hour', 123.created_at) AS trunc_created_at
FROM 123abc 123
WHERE 123.expires_at > date_trunc('day', GETDATE())   INTERVAL '2 days'

Is it possible, from here, to then do an 'and' or 'between' to do something like

and 123.expires_at < date_trunc('day', GETDATE())   INTERVAL '28 days'

Obviously, that doesn't work, but is there a way to do this using number of days instead of a specific date? I'm pretty new to SQL and have been playing with this for 30 minutes, including a dozen google queries, and can't seem to find a way to make something like this work. Only using between and a specific date range.

CodePudding user response：

If you are just trying to look back 28 days you can just subtract that from GETDATE

SELECT CAST(GETDATE() - 28 AS DATE)

So if you wanted to look back 28 days it would be something like this

SELECT
*
FROM TABLE
WHERE 1=1
AND DATE BETWEEN GETDATE() AND GETDATE() - 28

CodePudding user response：

I figured out how to do what I wanted

WHERE 123.expires_at > (date_trunc('day', GETDATE()) INTERVAL '2 days') AND 123.expires_at < (date_trunc('day', GETDATE()) INTERVAL '21 days')