How can I pull data from a SQL Database that spans an academic year?

Question

Basically, I want to pull data from August to May for a given set of dates. Using the between operator works as long as I do not cross the year marker (i.e. BETWEEN 8 AND 12 works -- BETWEEN 8 AND 5 does not). Is there any way to pull this data? Here is the SQL Query I wrote:

SELECT count(*), MONTH(DateTime)
FROM Downloads
WHERE YEAR(DateTime) BETWEEN 2009 AND 2010 AND MONTH(DateTime) BETWEEN 8 AND 5 
GROUP BY MONTH(DateTime)
ORDER BY MONTH(DateTime)"

Any help is appreciated.

Thanks,

Eric R.

Answer 1

Using BETWEEN with YEAR() and MONTH() is going to ruin any chance of using indexes on that column anyway. I would use:

SELECT
    COUNT(*) AS [count],
    YEAR(my_date) AS [year],
    MONTH(my_date) AS [month]
FROM
    Downloads
WHERE
    my_date >= '2009-08-01' AND
    my_date < '2010-06-01'
GROUP BY
    YEAR(my_date),
    MONTH(my_date)
ORDER BY
    YEAR(my_date), MONTH(my_date)

(I used my_date because I can't bring myself to refer to a column as DateTime) :)

Answer 2

SELECT count(*), MONTH(DateTime)
FROM Downloads
WHERE DateTime>'2009/8/1 00:00:00' AND datetime<'2010/6/1 00:00:00'
GROUP BY MONTH(DateTime)
ORDER BY MONTH(DateTime)