I would have approached it a bit different. I would have applied the logic to the dates in variables before moving to the actual statement i.e.
DECLARE @StartDate DATE, @EndDate DATE IF DATEPART(dd, GETDATE()) = 1 --1st of month BEGIN SET @StartDate = DATEADD(mm, -1, GETDATE()) --1st of prev month SET @EndDate = GETDATE() END ELSE BEGIN SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) --1st of this month SET @EndDate = DATEADD(dd, 1, GETDATE()) END
Then an easy WHERE clause would have been
datefield >= StartDate AND datefield < EndDate
4 People are following this question.