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 <month and day> value is earlier
than the birth <month and day> value. The <month and day> 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 Web site (http://www.cs.arizona.edu/~rts/).
Enjoy!
End of Article
Prev. page
1
[2]
next page -->
