I have custom table, which stipulates in days how long to retain records for. I need to know how I can pass in a variable into a BETWEEN statement to returns only the records from today inclusive, -variable.
WHERE (MessageDate BETWEEN GETDATE() AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), - 7))
-7 in this instance would be 7 days from today, and is meant to be a parameter which I pass in.
You don't need the DATEDIFF function, you can use just DATEADD, for which the correct parameter order you can find here.
So, your WHERE clause can look like this:
DECLARE @parameter INTEGER
SET @paramenter = -7
...
WHERE
(MessageDate BETWEEN GETDATE ()
AND DATEADD(day, @parameter, GETDATE())
)
Here is a SQLFiddle. (updated fiddle)
Edit:
Also, the BETWEEN part of the query should have an older date first ( DATEADD(day, -7, GETDATE() ) and then a more recent date ( GETDATE() ).
This is why, if you will always have a negative parameter to pass on, you will have to switch the order of the dates in the WHERE clause and use them like this:
WHERE
(MessageDate BETWEEN DATEADD(day, @parameter, GETDATE()
AND GETDATE())
)
But, if you might have to pass both positive and negative parameters, then use this:
WHERE
(MessageDate BETWEEN DATEADD(day, @parameter, GETDATE()
AND GETDATE())
OR
MessageDate BETWEEN GETDATE ()
AND DATEADD(day, @parameter, GETDATE())
)
Using OR you will have both cases covered, the one in which you send a positive parameter and the one where you send a negative parameter.
These cases are mutually exclusive, so only one condition will ever return results.
Substraction from GETDATE() results in DAYS, therefore you can use the following:
DECLARE @param INTEGER
SET @param = 7
--
WHERE cast(MessageDate as datetime) >= GETDATE()- @param
AND cast(MessageDate as datetime)<= GETDATE()