Previously, I wrote about grouping transactions by month
. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month.
There are two parts to this equation: First, what is the best way to declare parameters that will be used to indicate which month you are looking for? Second, how can we efficiently and easily make use of those parameters to get back the data we need?
Let's take a look at some approaches, both recommended
And, please: if you are not using the DATETIME data type to store dates in your tables, don't even bother reading further -- fix your design first!
Month Parameter Options
@YearMonth as char(6)
If you know me at all, you know that I will advise not to use this approach. We’ll need to parse and validate the values passed into ensure that they are valid, and we need to somehow communicate exactly which format to use when setting the parameter values. For example, is it “MM-YY”, or “MMYYYY”, or “MM/YY”, or “YYYYMM”, etc? It simply makes no sense to introduce string formatting conventions and parsing and validating into the equation when you simply don’t need to. Don’t do it.
@Year as int, @Month as int
This approach works fine, since now we have no string parsing or ambiguity to deal with. It is very clear that Year and Month are both simply numeric values. We can do a quick check to ensure that are @Month value is between 1 and 12, and that our @Year value is within our desired range as well.
As we will see later, an optimal solution will require finding the first day of the month requested. How can we do that given a @Year and a @Month value? Simple, you can either use the Date function here
Declare @FirstDayOfMonth datetime
Set @FirstDayOfMonth = dbo.Date(@Year,@Month,1)
… or you can apply the same logic using DateAdd() like this:
Declare @FirstDayOfMonth datetime
Set @FirstDayOfMonth = dateadd(month,((@Year-1900)*12)+@Month-1,0)
Either way, the result is the same: for a given @Year and @Month, the first day of that month (at midnight) is returned. If you aren’t sure how this formula works (or even if it will work at all), just test it out.
@DateInMonth as datetime
Here, we can simply accept any datetime value, and use the year and month of that date to indicate which month should be returned. A user can pass in the first day of the month, the last day, or any day in between – it doesn’t matter. This works well because we know the parameter will be a valid date, and we can quickly and simply get the year/month of the @DateInMonth parameter using the Year() and Month() functions:
Declare @Year int, @Month int
Select @Year = Year(@DateInMonth), @Month = Month(@DateInMonth)
We can also get the first day of the month for a given @DateInMonth quite easily, using another DateAdd() trick:
Set @FirstDayOfMonth = DateAdd(month, DateDiff(month,0, @DateInMonth), 0)
So, accepting a dateTime parameter to indicate which month you are after is also a good technique to use.
WHERE Clause Options
Now that we have the @Year, @Month and @FirstDayOfMonth values ready to go, we must decide how to use these values to write our query. Here are some options (again, both good
where Year(TranDate) =@Year and Month(TranDate) = @Month
This approach will work fine, but it will not be able to use any indexes that might exist on your TranDate column. Thus, it will not be as efficient as possible. Every single TranDate value must be retrieved from your table, the Year() and Month() formulas must be applied, and then the results must be tested to determine if that row should be returned or not. Overall, avoid wrapping indexable columns in functions such as these in your criteria.
where convert(varchar(6), TranDate, 112) = convert(varchar(6), @FirstDayOfMonth)
-- or --
where convert(varchar(6), TranDate, 112) = left(@Year,4)+right('0' + left(@Month,2),2)
-- or --
where convert(varchar(6), TranDate, 112) = @YearMonth
These WHERE clauses all convert the TranDate to a VARCHAR in the format of YYYMMDD, but truncated at 6 characters, resulting in a YYYMM formatted string. Then, using different methods, that string is compared to a YYYYMM representation of the month we'd like to return. This is a bad approach to take because it requires that every TranDate be converted to a string in order to filter the rows, thus no indexes can be used. Using any conversions or string manipulations to filter data when better options exist is never the way to go. At worst, it is also very confusing to work with since it can be unclear exactly what some of these convert() functions and string expressions are doing.
where TranDate between @FirstDayOfMonth and @LastDayOfMonth
What if we calculate, for a given @Year and @Month, the first and last day of the month, store them in variables, and then use that range for our criteria? This will let us use indexes, it will be clear and concise, and it makes good sense.
However, there is a minor flaw in this logic: Suppose @Year is 2006, and @Month is 1. If we set @LastDayOfMonth to ‘2006-01-31 12:00:00 AM’, any transactions after 12:00:00 AM on the last day of the month will not be included! We could calculate the very last 1/300th of a millisecond on the last day of the month to use as our upper limit, but there is an even easier way ...
where TranDate >= @FirstDayOfMonth and TranDate < @FirstDayOfNextMonth
Here, we won’t have any issues with times other than midnight leading to missed transactions.
We already have the @FirstDayOfMonth, all we need is the @FirstDayOfNextMonth. That is easily obtained by using DateAdd() to add one month to the @FirstDayOfMonth, as shown:
Set @FirstDayOfNextMonth = DateAdd(month, @FirstDayOfMonth,1)
With that, we are all set! Indexes can be used and any datetime value within our range will be properly selected. I highly recommend using this approach whenever you need to select dates for a single month; there is no need to force date computations upon all values in your table when a simple range is all you need.
Other Time Periods
The same approach can be applied if you need to allow for a range of months to be specified, not just a single month. Simply calculate the first day of the starting
month, and then calculate the first day of the month after
month. Then, you can use that date range to filter your data as shown above.
You should also follow these same guidelines if you'd like to filter data by a specified year, quarter, or week. "Year-to-date" filtering will also work the exact same way.
At the end of the day, accomplishing our task is very simple:
1. Accept either @Year and @Month parameters or a single @DateInMonth parameter to indicate which month should be returned
2. Calculate the @FirstDayOfMonth and the @FirstDayOfNextMonth using those parameters
3. Select your rows with a WHERE clause that returns transaction dates equal to or greater than @FirstDayOfMonth, and less than @FirstDayOfNextMonth, to ensure that all dates at the end of your range are captured and that indexes can be used.
Good luck, and remember: Declare your parameters to be clear and precise, and write your WHERE clauses to be as efficient and accurate as possible.