Selecting dates that are between the previous 12 to 9 month range

Question

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.

Answer 1

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.

Answer 2

If you are using SQL Server 2012 or later then you can use DATEFROMPARTS to help:

WHERE READDATE >= DATEADD(MONTH,-12,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1))
  AND READDATE <  DATEADD(MONTH, -9,DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1))

Minor modifications will make this work for calendar quarters too instead if rolling three month periods.

Before 2012 DATEFROMPARTS did not exist though for this case there is an easy hack with string casting:

WHERE READDATE >= DATEADD(MONTH,-12,CAST(CONVERT(CHAR(7),GETDATE(),121) AS DATE))
  AND READDATE <  DATEADD(MONTH, -9,CAST(CONVERT(CHAR(7),GETDATE(),121) AS DATE))

Answer 3

I'm wary about using DATEADD and DATEDIFF in the WHERE clause due to potential impact on estimates:

• Performance Surprises and Assumptions : DATEDIFF
• Performance Surprises and Assumptions : DATEADD

Also, if you care about performance, I've proven that using things like FORMAT() or CAST/CONVERT() to a string in order to strip time from a datetime is sub-optimal:

• What is the most efficient way to trim time from datetime?
• Trimming time from datetime – a follow-up
• FORMAT() is nice and all, but...

And since you are not on SQL Server 2012 or greater, you can't use DATEFROMPARTS(), which is probably the cleanest way to do that. But there is still a tidy way to get the beginning of the current month (first, subtract days from today, then convert to date), and from there it's trivial to subtract a year, and then 9 months, and build a proper WHERE clause:

DECLARE @ThisMonth date = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE());

SELECT ... FROM dbo.tablename ...
  WHERE READDATE >= DATEADD(YEAR,  -1, @ThisMonth)
    AND READDATE <  DATEADD(MONTH, -9, @ThisMonth);

I wouldn't ever use >= and <= for a date range query. This is equivalent to BETWEEN, and can lead to all kinds of inaccuracies, like trusting that data could only be recorded up to 12 seconds before midnight (?). Don't try to find data at or before the "end" of a period - the way you want to think about this is that you just want everything before the beginning of the next period.

Some other reading on date/range queries:

• Bad Habits to Kick : mis-handling date / range queries
• Bad Habits to Kick : Using shorthand with date/time operations
• #BackToBasics : Dating Responsibly

Answer 4

You can check this solution, it'll give you all dates between the selected range.

DECLARE @startdate DATETIME
    ,@enddate DATETIME

SET @startdate = '1/1/2012'
SET @enddate = '1/15/2012'

;WITH cte
AS (
    SELECT @startdate DateVal

    UNION ALL

    SELECT DateVal + 1
    FROM cte
    WHERE DateVal + 1 <= @enddate
    )
SELECT c.DateVal, COALESCE(sum(quantity),0)
FROM cte c
LEFT JOIN YourTable t ON c.DateVal = t.ProductionDate
GROUP BY c.DateVal