We have a simple contact table CONTACT - FirstName - LastName - DateOfBirth It’s wrapped as a C# class. (with appropriate constructors and other logic to instantiate from the database)
class Contact
{
public string FirstName;
public string LastName;
public DateTime DateOfBirth;
...
}
We want to list all the contacts who are over 18. So we do something like…
foreach(Contact currentContact in rangeOfContacts)
if((DateDiff(DateInterval.Month, currentContact.DateOfBirth, DateTime.Now) / 12) > 18)
Console.WriteLine("{0}", currentContact.LastName);
Basically, we’re pulling the date of birth from the database, and then using our own code to calculate the age. Of course we could wrap the age up as a property of Contact, and we could even extract the year difference calculation method into a utility class in case we need to do other whole year based methods. I’ve seen this code a lot in my time. But why not let the database do it? Create a calculated column on the table…
CREATE TABLE Contact(
FirstName VARCHAR(20),
LastName VARCHAR(20),
DateOfBirth DATETIME,
Age AS Cast(DateDiff("mm", DateOfBirth, GetDate())/12 AS INTEGER)
)
It’s pretty simple, and it means everyone uses the same method. Yes - it does bend normalization rules, but I think it increases readability far more. Plenty of other applications for it also….
CREATE TABLE ExpiryThingy
(
ExpiryDate DATETIME,
Expired AS (CASE
WHEN (ExpiryDate < getDate()) THEN '** Expired **'
ELSE '** Valid **'
END)
)
Go to town!