Sql-server – Selecting dates that are between the previous 12 to 9 month range

datesql servert-sql

I need help selecting dates that are between the previous 12 to 9 month range. As an example if today is 2-22-2017 I need to pull a date range from 2-1-2016 to 3-31-2016.

I can select all dates from the previous 12 months using this:

WHERE READDATE >= DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH,-12,GETDATE())), 0)
AND READDATE <= DATEADD(s,-12,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0))

But I can't figure out how to exclude dates after the previous 9 months giving me that 3 months window.

Best Answer

This should work:

...
WHERE 
    -- >= 2016-02-01 00:00:00.000
    READDATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 12, 0)
    -- < 2016-04-01 00:00:00.000
    READDATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 10, 0) 
...

Please note that with dates it is usually best to compare it this way:

X >= lower bound
    AND
X < upper bound

This is why I change <= '20160331' to < '20160401'. While it works fine with your sample using what seems to be DATE types, it may give incorrect results using dates with a TIME part.

Related Question