# Calculate Age (in years)

• Comments posted to this topic are about the item Calculate Age (in years)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• From the Article:

See Comments in the code for more details.

Awesome. I don't very often see such a useful bit of header information. You've provided a succinct yet accurate purpose, provided some usage examples, gave credit to your resources, documented places to get more information, and documented the revisions. Nicely done, Alan.

--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".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• Nice job, Alan. It may be sightly more complicated (but not much more) than an SF, but I'll take performance over simplicity every time. After all, when you multiply your savings by how many executions you have per day, it really adds up fast.

• What should the results for these test cases be?

`SELECT age FROM dbo.YearsApart('2/28/2002','02/28/2003');`

`SELECT age FROM dbo.YearsApart('2/28/2003','02/28/2004');`

`SELECT age FROM dbo.YearsApart('2/29/2004','02/28/2005');`

`SELECT age FROM dbo.YearsApart('03/01/2004','03/01/2005');`

Edit : Seems that how you handle leaplings depends on your country.

In US, Hong Kong, Taiwan and New Zealand, dbo.YearsApart('2/29/2004','02/28/2005'); should be 1

In England and Wales, dbo.YearsApart('2/29/2004','02/28/2005'); should be zero.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

• Interesting way to do this. I'll need to compare with our method. Thanks.

What should the results for these test cases be?

`SELECT age FROM dbo.YearsApart('2/28/2002','02/28/2003');`

`SELECT age FROM dbo.YearsApart('2/28/2003','02/28/2004');`

`SELECT age FROM dbo.YearsApart('2/29/2004','02/28/2005');`

`SELECT age FROM dbo.YearsApart('03/01/2004','03/01/2005');`

Edit : Seems that how you handle leaplings depends on your country.

In US, Hong Kong, Taiwan and New Zealand, dbo.YearsApart('2/29/2004','02/28/2005'); should be 1

In England and Wales, dbo.YearsApart('2/29/2004','02/28/2005'); should be zero.

Thank you for the post. The logic (which is open to debate) is that leap years are a day longer than non leap years. For non leap years something has aged one year on the 365th day, for leap years it has aged one year on the 366th day.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Jeff Moden (4/10/2016)

From the Article:

See Comments in the code for more details.

Awesome. I don't very often see such a useful bit of header information. You've provided a succinct yet accurate purpose, provided some usage examples, gave credit to your resources, documented places to get more information, and documented the revisions. Nicely done, Alan.

Ed Wagner (4/10/2016)

Nice job, Alan. It may be sightly more complicated (but not much more) than an SF, but I'll take performance over simplicity every time. After all, when you multiply your savings by how many executions you have per day, it really adds up fast.

Thank you both very much! That means a lot.

I stole the comment section layout/formatting from you Jeff 😛

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

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