t-sql 2012 calculate age

Question

Post reply

t-sql 2012 calculate age

wendy elizabeth

SSCoach

Points: 16915
More actions
August 31, 2014 at 9:30 pm

#289563

In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

caffeinated Ten Centuries Points: 1271 More actions · Answer 1

You'll want to look into DATEDIFF function: http://msdn.microsoft.com/en-us/library/ms186724.aspx

Try this:

DECLARE @birthday_list TABLE(

date_of_birth datetime

)

INSERT @birthday_list(

date_of_birth

)

VALUES('1980-05-03')

,('1999-11-23')

,('2010-04-14')

,('2000-09-29')

SELECT

date_of_birth

,age = DATEDIFF(YEAR, date_of_birth, GETDATE())

FROM @birthday_list

--RESULT:

--date_of_birth age

--1980-05-03 00:00:00.000 34

--1999-11-23 00:00:00.000 15

--2010-04-14 00:00:00.000 4

--2000-09-29 00:00:00.000 14

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick thought, the datediff function sometimes plays tricks on us, not to be trusted blindly, look at this sample

😎

USE tempdb;

GO

DECLARE @TEST_DATA TABLE

(

TD_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,TD_NAME VARCHAR(10) NOT NULL

,TD_DOB DATETIME NOT NULL

);

INSERT INTO @TEST_DATA (TD_NAME,TD_DOB)

VALUES

('AAA','2002-01-15')

,('BBB','2002-03-15')

,('CCC','2002-05-15')

,('DDD','2002-07-15')

,('EEE','2002-09-15')

,('FFF','2002-11-15')

,('GGG','2003-01-15');

DECLARE @TODAY DATETIME = GETDATE();

SELECT

TD_ID

,TD_NAME

,TD_DOB

,DATEDIFF(MONTH,TD.TD_DOB,@TODAY) / 12.0 AS AGE_WITH_FRACTION

,FLOOR((DATEDIFF(MONTH,TD.TD_DOB,@TODAY) / 12.0)) AS AGE_FULL_YEARS

,FLOOR((DATEDIFF(MONTH,TD.TD_DOB,@TODAY) / 12.0))

+ (DATEDIFF(MONTH,TD.TD_DOB,@TODAY) % 12.0) / 100 AS AGE_YEAR_MONTH

FROM @TEST_DATA TD;

Results

TD_ID TD_NAME TD_DOB AGE_WITH_FRACTION AGE_FULL_YEARS AGE_YEAR_MONTH

------ -------- ----------------------- ------------------ --------------- ---------------

1 AAA 2002-01-15 00:00:00.000 12.666666 12 12.080000

2 BBB 2002-03-15 00:00:00.000 12.500000 12 12.060000

3 CCC 2002-05-15 00:00:00.000 12.333333 12 12.040000

4 DDD 2002-07-15 00:00:00.000 12.166666 12 12.020000

5 EEE 2002-09-15 00:00:00.000 12.000000 12 12.000000

6 FFF 2002-11-15 00:00:00.000 11.833333 11 11.100000

7 GGG 2003-01-15 00:00:00.000 11.666666 11 11.080000

caffeinated Ten Centuries Points: 1271 More actions · Answer 3

True, date conversions get tricky depending on context. But taking into account students, and school system, I'm guessing that level of accuracy isn't necessary.

Your example introduces division by floats and integers...that complicates your results. I usually find it better to keep things simple (at least at first design).

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 4

wendy elizabeth (8/31/2014)
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?

Dear Wendy,

I have already created a function related to this. Please use below link :

http://www.sqlservercentral.com/scripts/Date+Difference/113184/[/url]

i hope this will helpful & fulfill your need.

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

caffeinated (8/31/2014)
True, date conversions get tricky depending on context. But taking into account students, and school system, I'm guessing that level of accuracy isn't necessary.
Your example introduces division by floats and integers...that complicates your results. I usually find it better to keep things simple (at least at first design).

The purpose of my post was to show that DATEDIFF with YEAR isn't always accurate, not to provide a full solution. The OP question is too unclear as there are few conventions for accounting age such as "in this year" etc. This need to be detailed.

😎

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Lot's of folks like to write about this sort of stuff, mostly re-hashing the same old, same old.

Me, I prefer to rely on the results posted by a well-known SQL expert.

http://www.solidq.com/efficiently-querying-calculating-using-sql-server-datetime-columns/

There's an age calculation example at the end of that article and you can't go wrong taking Adam Machanic's advice.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St