Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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)
share|improve this answer
    
here I was writing an answer and then I read your code and it does exactly what I was about to write. +1 –  Malachi Apr 28 at 15:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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