Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  General SQL Server Forums  New to SQL Server Programming  Simple Age Calculate Reply to Topic  Printer Friendly
Author  Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

 Posted - 03/03/2013 :  01:35:38 Hey guys,I've seen and used a lot of elegant queries to calculate age. I was thinking about it today though and it seems you should be able to do simple datediff and divide by 365.25 to get a person's age like this:declare @BirthDate date, @CheckDateA date, @CheckDateB dateselect @Birthdate='1971-04-14',@CheckDateA='2013-04-13',@CheckDateB='2013-04-14'select CAST(datediff(dd, @BirthDate, @CheckDateA)/365.25 AS INT), CAST(datediff(dd, @BirthDate, @CheckDateB)/365.25 AS INT)Am I oversimplifying this? It seems like this is a relatively easy way to get someone's age.

LoztInSpace
Aged Yak Warrior

940 Posts

 Posted - 03/03/2013 :  04:39:00 Yes, datediff would be the way to do it. You can use different datepart depending on your requirement. YYYY would give you the years if that's what you want.

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 03/03/2013 :  11:25:31 It would not be perfect - for example, this is not quite right, because of 2012 being a leap year:```declare @BirthDate date, @CheckDateA date, @CheckDateB date select @Birthdate='20110225' ,@CheckDateA='20120225' ,@CheckDateB='20130225' select CAST(datediff(dd, @BirthDate, @CheckDateA)/365.25 AS INT), CAST(datediff(dd, @BirthDate, @CheckDateB)/365.25 AS INT)```On Feb 25, 2012 the age is reported as 0, yet on Feb 25, 2013, the age is reported as two.An unambiguous way to calculate age (which relies on the ordering when date is expressed in YYYYMMDD format being correct when expressed as an int) is as follows:```declare @BirthDate date, @CheckDateA date, @CheckDateB dateselect @Birthdate='20110225' ,@CheckDateA='20120225' ,@CheckDateB='20130225' SELECT (CAST(CONVERT(CHAR(8), @CheckDateA, 112) AS INT) - CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT) )/10000, (CAST(CONVERT(CHAR(8), @CheckDateB, 112) AS INT) - CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT) )/10000 ``` Edited by - James K on 03/03/2013 11:41:01

flamblaster
Constraint Violating Yak Guru

384 Posts

 Posted - 03/03/2013 :  11:56:07 Thanks for both your comments! James, that makes sense...this is what I was looking for. I use the latter that you posted right now for age calculation and know that it works well. I just like trying to simplify wherever possible. But your explanation makes sense. I thought I was handling the leap year by using 365.25 instead of 365. I see why that doesn't work now though.And Lozt...using Datepart for years wouldn't work because it doesn't handle the difference between "before" anniversary and "after" anniversary. It just compares the raw number of years between the start date's year and the end date's year.Thanks! Edited by - flamblaster on 03/03/2013 12:00:21

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 03/03/2013 :  17:08:08 You are very welcome - glad you have it working the way you want.
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC