Is there a way to easily calculate the number of years between two dates.

Question

Is there a way to easily calculate the number of years between two dates.

martin.goodrich

Newbie

Points: 2
More actions
April 23, 2008 at 9:44 am

#206418

Hi,
I am a real newbie to SQL server and I would surely appreciate some assistance.
If for example I have a Date of Birth and I need to calculate the number of years between this date and a given reference date is there a nice simple piece of SQL that may yield this?
Really up against things and would be grateful of any assistance.
Best regards,
Martin.

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

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

search on phrases like 'date arithmetic' or 'date calculation' or 'date formatting' for ideas on how to solve your problem. multiple ways to solve your issue, and they've all been published by other people.

Don't get lost in the forest looking at all the pretty trees - remember to focus on your specific question. You can come back to learn the rest of the stuff later.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Heh... skip the "trees"... try this...

SELECT ABS(YEAR(DATEADD(dd,DATEDIFF(dd,@BirthDate,@ReferenceDate),0)-1)-1900)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Jeff's function will most likely do what you need, but keep in mind that calculations of age for "Leaplings" (people born on 29 Feb) can be problematic.

It's not a problem with the math, it's a problem with local ordinances about eligibility for driver's licenses, drinking age, Social Security, mandatory IRA withdrawals, etc. Some calculate the age in non-leap years based on 28 Feb, some on 1 Mar. (Jeff's uses 1 March, because 1900 wasn't a leap year.)

If that won't matter for your calculation, then ignore it and use his function. If it will matter, then you'll need to look up the pertinent laws and factor them into the thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

martin.goodrich Newbie Points: 2 More actions · Answer 4

martin.goodrich

Newbie

Points: 2

April 29, 2008 at 11:02 am

#808548

Thanks Jeff.

Works a treat.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

Thanks for the feedback... like Gus said, though... leap years can wreak a bit of havoc if they're important.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)