DATETIME Calculations, Part 5

For the past four months, I've discussed various calculations involving datetime data. This month, I conclude the series by introducing techniques to calculate working days, age, and next birthday date. Later in the article, I also point you to additional resources where you can find more information about temporal data manipulation.

Calculating Working Days

To tackle this problem, we can write a function called fn_workingdays that calculates the number of working days in an input period. The function accepts three input parameters:

The output of the function is an integer representing the number of working days in the input period. Working days are weekdays 1 through 5, based on the input first working day of the week (@datefirst). For example, if @datefirst is set to 1 (Monday), Monday through Friday are considered working days, whereas Saturday and Sunday are considered nonworking days. If @datefirst is set to 7 (Sunday), Sunday through Thursday are considered working days, whereas Friday and Saturday are considered nonworking days.

Note that the recommended approach to handle such calculations is to maintain an auxiliary table of dates, with attributes signifying whether a certain date is a working day. This approach is flexible because it can also take holidays and other special cases into consideration. However, for the purpose of this discussion, we'll narrow the problem to a simpler case in which you need to take into consideration weekdays 1 through 5 (based on the given first working day of the week)—not weekdays 6 and 7. We won't handle holidays and other special cases. This problem is solvable with a single expression that doesn't involve the need to query any auxiliary tables.

You can find my suggested solution—which is, of course, only one of many options—in Web Listing 1. (You might try solving the problem on your own before looking at my solution.) The logical approach I've taken is as follows:

As for the more technical description of the function's definition, I'll start with the query defining the innermost derived table D1 and go outward. The query defining D1 calculates two columns— days, which is the number of days in the input period, and from_weekday, which is the weekday number of @from_dt based on the input first working day of the week (@datefirst). The calculation of days is straightforward: using a simple DATEDIFF function that calculates the difference in terms of days between @ from_dt and @to_dt, plus one. As for the calculation of from_weekday, by now you should be comfortable with this calculation of a language-independent weekday number, which I explained in "DATETIME Calculations, Part 2."

The purpose of the query defining D2 is to calculate the to_weekday column: from_weekday plus the number of days in the remaining part of the week, minus 1. Note that to_weekday can be higher than 7, allowing for simpler calculations in the outermost query. For example, if from_weekday is 5 and there are 4 days in the remaining part of the week, to_weekday will be 8. Therefore, figuring out whether days 6 and 7 are within the remaining part of the week at the end of the period is simple.

The outermost query then performs the final calculation of the number of working days. As I described earlier, the result amounts to the number of whole weeks times 5 (days/7*5), plus the number of days in the remaining part of the week (days%7), minus 1 if 6 is within the input period (6 between from_weekday and to_weekday), minus 1 if 7 is within the input period (7 between from_weekday and to_weekday).

To verify that the calculation is correct, let's test the function. Run the code in Web Listing 1 to create it. (Make sure you're connected to the database in which you want the function to be created—for example, tempdb.) Next, run the following code:

SELECT dbo.fn_workingdays(‘20070212', ‘20070223', 1) 

This period covers 12 days (starting on a Monday and ending on a Friday), two days of which are nonworking days. (Monday is set as the first working day of the week.) You get 10 working days back, as expected. Next, run the following code:

SELECT dbo.fn_workingdays  (‘20070212', '20070223', 7)

It's the same period as before, but now three days within the period are nonworking days. (Sunday is set as the first working day of the week.) You get nine working days back, as expected.

Calculating Age

The next problem is calculating the age of a person, given an input birth date (@ birth_date). Of course, when you want to calculate age, you also need an event date. We'll use today (GETDATE) as the event date. Web Listing 2 shows an age-calculation technique that I learned from SQL Server MVP Aaron Bertrand.

The expression first uses the DATEDIFF function to calculate the difference in terms of years between the birth date (@ birth_date) and the event date (GETDATE, in our example); call this difference diff_years. Note that DATEDIFF will simply subtract the birth year from the event year, so if the event date is earlier than the birthday date in the event year, you'll need to subtract 1 year from diff_years. You do this in the second part of the calculation—subtraction of 1 year from diff_years if the event value is earlier than the birth value. The value is an integer in the form mmdd, generated by multiplying the month portion of the date by 100 and adding the day portion of the date. Interestingly, this calculation works perfectly well even when leap years are involved.

To test the calculation, you can play with different dates for both the birth date and the event date. For example, I ran the code in Web Listing 2 on Feb 16, 2007, providing my birth date (i.e., February 12, 1971) for the @birth_date variable, and got my correct age (i.e., 36) back. I ran it again, setting @birth_date to ‘19710216' and still got 36 back. I ran it again, setting @birth_date to "19710217," and got 35 back.

You can also use this calculation in a query against a table, in which each row holds the birth date of a different person. To demonstrate this method, first run the code in Web Listing 3, adding two employees to the Employees table in the Northwind database—George Leaping, born on Feb 29, 1972, and Mary Today, born today. Then, run the query in Web Listing 4 and verify that the ages of the employees are correct. I ran this code on Feb 16, 2007, and got the output that Table 1 shows. Of course, you'll get different output because you're running the code on a different date.

Calculating Next Birthday Date

The third and final problem is calculating a person's next birthday date (in respect to today's date, assuming the birth date is today or earlier). I should define the term "next" accurately. If the person's birthday date already occurred this year prior to today, the calculation should produce that person's birthday date next year; otherwise, it should produce this year's birthday date. For example, I was born on February 12, 1971. If the current year is 2007, running the calculation after February 12 should produce February 12, 2008; if I run the calculation on or before February 12, it should produce February 12, 2007.

Also, you need to consider the special case of someone born on February 29 in a leap year, like our George Leaping, who was born on February 29, 1972. Naturally, if the target year is a leap year, the calculation should produce February 29 of that year. The question is how you want to treat a case in which the target year isn't a leap year. Note that if you use the DATEADD function to add a certain number of years to such a date, SQL Server's default behavior is to produce February 29 if the target year is a leap year, and February 28 if it isn't. If your application is such that you're supposed to produce March 1 if the target year isn't a leap year (e.g., for legal purposes), you'll need to identify this special case and adjust the target date. (I'll make that assumption for this article's technique.)

I'll use the Employees table in the Northwind database, with the two additional rows that we added earlier by running the code in Web Listing 3. The query in Web Listing 5 demonstrates how to calculate the next birthday date for each employee. The code defines a CTE called Args1, which queries the Employees table and calculates two result columns. One is a column called Diff, which holds the difference in terms of years between the birth year and the current year. The other is a column called Today that holds today's date at midnight.

The code defines a second CTE called Args2, which queries Args1 and calculates two result columns called BDCur and BDNxt. BDCur is calculated by adding Diff years to BirthDate, and BDNxt is calculated by adding Diff + 1 years to BirthDate. In more logical terms, BDCur represents the employee's birthday date in the current year and BDNxt represents the employee's birthday date in the following year. Remember that the default behavior of the DATEADD function—in a case in which the source date is February 29 in a leap year and the target year isn't a leap year—is to produce a target date of February 28. If you need to produce March 1 in such a case, you need to add logic to perform the adjustment. This is the purpose of the CTE called Args3; the code defining Args3 queries Args2 and adjusts BDCur and BDNxt if they need adjustment. This adjustment happens through CASE expressions that add one day if the special case is identified. (The source day part is 29, and the target day part is 28.)

Finally, the outer query simply checks which of the two values (BDCur or BDNxt) is the one that really represents the employee's next birthday date. If the current year's birthday date (BDCur) is greater than or equal to today, BDCur is returned. Otherwise, the next year's birthday date (BDNxt) is returned. I ran this query on February 16, 2007, and got the output that Table 2 shows.

As you can see in the output, Nancy Davolio, born on December 8, 1948, will celebrate her next birthday on December 8, 2007. Laura Callahan, born on January 9, 1958, will celebrate her next birthday on January 9, 2008. George Leaping, born on February 29, 1972, will celebrate his next birthday on March 1, 2007. Mary Today, born today, celebrates her next birthday today. After you're done experimenting with these calculations, run the following code to delete the rows for George Leaping and Mary Today from the Employees table:

DELETE FROM Northwind.dbo.  Employees WHERE EmployeeID > 9;

Now It's Up to You

There are so many other datetime-related calculations that I haven't covered—so many that I could continue covering the subject for quite a few more months. But five datetime articles in a row should give you enough tools to continue the explorations yourself.

For those of you who can't get enough of temporal calculations and queries, you can download the PDF version of the book "Developing Time-Oriented Database Applications in SQL" by Richard T. Snodgrass (Morgan Kaufmann Publishers, July 1999). Mr. Snodgrass made the PDF version of his book available for download for free at his website. Enjoy!

Corrections to this Article

(Diana May - June 1, 2007)

Table 2 contains a typo. The correct date for George Leaping should be as follows: