I think I am slipping up! In this article, I am trying to explain how something WORKS rather than how to DO something. Yeah, yeah, I know, I shouldn’t be posting this. It can cause erosion to my shallowness. Oh well! You live only once!
The question I am trying to answer is this: given two dates – a start date and and end date – how do you count the number of WEEKDAYS between those two dates, inclusive of start date and end date. Weekday being Monday-Friday. In other words, you want to exclude Saturdays and Sundays and see how many days there are between the start and end.
My normal instict/recommendation/thought process would be to use a calendar table. In another blog post I outlined a brilliant piece of query that I invented showing how to create a calendar table. (Ok, ok, you caught me, I didn’t INVENT it, I DISCOVERED it at someone else’s blog. Don’t get hung up on semantics, for crying out loud!!)
But there is another way without using a calendar table, which I am going to demonstrate below. That will be followed by my thoughtful, insightful, (add additional “full”s here) analysis of how the query works.
DECLARE @StartDate DATETIME; DECLARE @EndDate DATETIME; SET @StartDate = '20130601'; SET @EndDate = '20130630'; SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1) -- Part 1 -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -- Part 2 - (CASE WHEN DATEDIFF(dd,0,@StartDate) % 7 = 6 THEN 1 ELSE 0 END) -- Part 3 - (CASE WHEN DATEDIFF(dd,0,@EndDate) % 7 = 5 THEN 1 ELSE 0 END); -- Part 4
I marked each section in the query above as Part 1 through Part 4 so I can refer to them here.
Part 1: That is simply calculating how many days there are between @StartDate and @EndDate (inclusive). Simple, right?
Part 2: That is subtracting out twice the number of weeks between @StartDate and @EndDate. Well, almost. Really what it is subtracting out is the number of WEEK BOUNDARY CROSSINGS between @StartDate and @EndDate. A week boundary is crossed when you go from Saturday to Sunday. In case you are asking “Is that so? What if my calendar/locale/datefirst/some other setting is different?” the answer still is the same. Week boundary crossing always occurs when you go from Saturday to Sunday.
So now we are almost there. Calculated the total number of days, and subtracted two days for each week boundary that we cross. But there are two pieces of adjustments that we need to do. And those are Parts 3 and 4.
Part 3: Imagine that your @StartDate is on a Sunday. And just to make it simple, assume your @EndDate is two days later, on Tuesday. You want to count the Monday and Tuesday, but you want to exclude Sunday in your calculations. So you want to get the result as 2, not 3. If you had only the first two parts, and given that there are no week boundary crossings in this simple example, you will get the result to be 3. This third piece corrects for that anomaly.
By the way, if you want to know what that “DATEDIFF(dd,0,@StartDate) % 7 = 6” means: it is simply a way of figuring out if a given date is on a Sunday correctly regardless of server and locale settings. I promise to find a good blog from somewhere that describes how that works and copy it here pretending it is my own brilliant creation.
Part 4: By now, I probably don’t have to tell you what Part 4 is doing. If the @EndDate is on a Saturday, we want to remove that Saturday from the count, just like Part 3 removed the Sunday if @StartDate happened to be a Sunday.
There! Wasn’t that sweet!? If I find a good blog that explains this, I will post a link here and will claim that I copied it from there. Don’t want to take away anything from my shallowness, you know!
And until next time, “good bye and good night” (Or good evening, good afternoon, or good morning as the case may be.)