7
\$\begingroup\$

I've written the following query for SQL server:

SELECT YEAR(Meeting_Date) year, MONTH(Meeting_Date) month, COUNT(project) meetings
FROM
meetings
WHERE Meeting_Date BETWEEN
CAST(DATEPART(year,DATEADD(m,-6,GETDATE())) as varchar(4)) + '-' + CAST(DATEPART(month,DATEADD(m,-6,GETDATE())) as varchar(2)) + '-01'
AND 
DATEADD(d,-1,CAST(DATEPART(year,GETDATE()) as varchar(4)) + '-' + CAST(DATEPART(month, GETDATE()) as varchar(2)) + '-01')
GROUP BY YEAR(Meeting_Date), MONTH(Meeting_Date)
ORDER BY YEAR(Meeting_Date), MONTH(Meeting_Date)

This code counts up how many meetings there were for the six months prior to this one. It works perfectly, but I'm sure there's a better way of figuring the months than I have.

\$\endgroup\$

1 Answer 1

4
\$\begingroup\$

This is one of those things.... date manipulation in SQL.

For performance reasons, it is a pain to cast to NVarchar, etc. For readability reasons, everything is a pain.....

What I have found works best for me, is the following function:

DateAdd(d, 1 - DatePart(d, actualdate), actualdate)

The above subtracts the number of days needed to bring a date back to the first-of-the-month.

For example today, (the 28th April), would subtract 1 - 28 (or 27) days from the 28th, to get the 1st.

You can do your current month-manipulation to move months around once you are back on to the first-of-the-month, and then you don't have to worry about the month overflow problems..

For readability reasons, I would recommend you use variables, if you can:

declare @thismonth as Date = Convert(DateAdd(d, 1 - DatePart(d, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) as Date);
declare @firstmonth as Date = DateAdd(m, -6, @thismonth);

SELECT YEAR(Meeting_Date) year,
       MONTH(Meeting_Date) month,
       COUNT(project) meetings
FROM
    meetings
WHERE Meeting_Date BETWEEN @firstmonth AND @thismonth
GROUP BY YEAR(Meeting_Date), MONTH(Meeting_Date)
ORDER BY YEAR(Meeting_Date), MONTH(Meeting_Date)
\$\endgroup\$
1
  • \$\begingroup\$ here I was writing an answer and then I read your code and it does exactly what I was about to write. +1 \$\endgroup\$
    – Malachi
    Apr 28, 2014 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.