Efficient Age From Date of Birth Calculation

I have been playing around with MS SQL and I was wondering what would be the most efficient method to gather a users age.

Calculations involving dates should always be considered from their most tricky state, if you consider that you might need to take into account UTC time, leap years, time zones it all starts to add up to complexity that you can too easily trip up over. 

So my first thought when someone asks me to calculate the age from a DOB field is to "cheat" and use the built in languages tools. Most languages have some form of datediff function that can be used to calculate the time between two dates.  However is this going to be the most efficient method?

I thought I would test a couple of methods on MS SQL to see the performance changes.  All tests were performed on a low spec laptop on MS SQL Express on a set of 100 random date of births

First method

SELECT floor(datediff(day,[dob],current_timestamp) / 365.25)

Well this is nice and succinct but does suffer a bit from the fact that DATEDIFF rounds up.  I would love to just do DATEDIFF(year... but this will only provide the correct age once per year)

This method gave me the answer in 0.296 secs

Second method

SELECT (convert(int,convert(varchar(8),current_timestamp,112))
 - convert(int,convert(varchar(8),[dob],112)))/ 10000

Well some basic maths relying on Japanese date format gives me the same answer in 0.343 secs

Third method subtract the years from each other and then reduce by 1 if the

 SELECT CASE WHEN convert(int,substring(CONVERT(varchar(8), [dob] , 112),5,4)) >= convert(int,substring(CONVERT(varchar(8), CURRENT_TIMESTAMP , 112),5,4))
THEN convert(int,substring(CONVERT(varchar(8), CURRENT_TIMESTAMP , 112),1,4)) - convert(int,substring(CONVERT(varchar(8), [dob] , 112),1,4)) -1
ELSE convert(int,substring(CONVERT(varchar(8), CURRENT_TIMESTAMP , 112),1,4)) - convert(int,substring(CONVERT(varchar(8), [dob] , 112),1,4))
END
 This method took 0.420 seconds.

Looking at this the first two methods are for all intents and purposes identical to just the selecting the raw dob value, which indicates to me that there is very little efficiency to be gained in this process.  Even my messy CASE version is pretty swift.

Although it is fun to do this in the database it is really much easier to do this at the higher level and intial testing of this suggest to me that the overhead is so minimal that the methods I have outlined are only really useful for quick reporting when you just need the calculation and have an available SQL interface

Comments

Post a Comment

Popular posts from this blog

IE9 Intranet compatibility mode in Intranet websites

So you have written an HTML5 site on your local intranet with some lovely CCS3 and run it up in Firefox and you feel smug, all your HTML and CSS are perfectly formed, but you run it up in IE9 and all the CSS3 goodness has gone away leaving your lack luster IE7 version of your site.   Why is IE9 running in IE7 compatibility mode? IE9 has a hidden setting that forces it to run in compatibility mode when it encounters any intranet websites. Microsoft have detailed this behaviour in a Blog about what they call Smart compatibility mode . You can easily switch off the compatibility mode for specific machines using the internet tools mentioned in the article above, but most of the time developers do not have the luxury of applying corporate wide settings of this nature. Avoiding Smart compatibility with X-UA-Compatible Luckily there is a single line of HTML you can that you can use to override this behaviour: <meta http-equiv="X-UA-Compatible" content="IE=edge,c
Read more

User Interface Usability Checklist Part 2

So you made it through part 1 of the usability check list, how about part 2? 21. Poor Label Alignment If you have been through design 101 you have probably heard of the wonderful acronym for the basic principles of design, C.R.A.P. This of course stands for Contrast, Repetition, Alignment and Proximity. Proximity is very important when identifying which label belongs to which input. Each different form of alignment has its advantages and disadvantages. Left Alignment The label appearing on the left of a box is a common practice. This often helps with vertical space and keeping all of the elements of a form on a single page, which can be beneficial in aiding the user in form completion and making them aware of all of the possible options they can, or made need to provide information for. It does however have a few disadvantages. Long labels cause short label's input boxes to be far away from the end of the label, this damages that basic principle of proximity. In extreme
Read more

Procedural VS Object Oriented

What is the correct way to write code? If you have worked with PHP long enough you may well have come across the website  PHP The Right Way . This website is dedicated to helping developers learn the industry best practices. Interestingly another site appeared afterwards called  PHP The Wrong Way . In essence this website is a rebuttal to the many of the recommendations made in PHP The Right Way. With any sphere of learning it is important to keep an open mind, to listen and critically evaluate the received wisdom and determine if it is indeed wisdom or opinion built on shaky ground. There is one part of the argument I would like to focus on and this is the argument for and against " Always use object-oriented Programming ". While PHP The Right Way does not appear to explicitly state that you should always use an object oriented approach it is implicit from the examples and the recommendations to use design patterns. While there are programming approaches available othe
Read more