How can we calculate Age of employee?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 7,2000 » T-SQL » How can we calculate Age of employee?

Add to briefcase

26 posts, Page 2 of 3

How can we calculate Age of employee?

Expand / Collapse

Author

Message

Michael Valentine Jones

Posted Thursday, May 14, 2009 1:07 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 2,969, Visits: 10,615

These are links to my original post of the function from the prior post on this thread, plus another that you might find useful.

Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.
This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Post #717302

Posted Thursday, May 14, 2009 1:16 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

Here is one article on calculating age, Calculating Age. Give it a read as well as the discussion that followed. There may also be several more articles on calculating age on SSC, you might want to do a search of the site to see what else may turn up.

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #717313

Posted Friday, November 16, 2012 2:01 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:40 PM
Points: 14, Visits: 49

Simple way to compute your age accurately.

A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

In this query you exactly get the age as is it.
Example 1
DOB = 11/15/1987 and
datenow =11/15/2012 the result would be
AGE=25

Example 2
DOB = 11/14/1987 and
datenow =11/15/2012 the result would be
AGE=24

SO HERE ARE THE QUERY

DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'

SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1

END AS AGE

HOPE I CAN HELP! Smile

Smile

Post #1385545

Posted Friday, November 16, 2012 2:02 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:40 PM
Points: 14, Visits: 49

Simple way to compute your age accurately.

A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

In this query you exactly get the age as is it.
Example 1
DOB = 11/15/1987 and
datenow =11/15/2012 the result would be
AGE=25

Example 2
DOB = 11/14/1987 and
datenow =11/15/2012 the result would be
AGE=24

SO HERE ARE THE QUERY

DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'

SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1

END AS AGE

HOPE I CAN HELP! Smile

Smile

Post #1385546

Posted Wednesday, January 23, 2013 8:33 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 73, Visits: 152

The following combinations give a negative result (that is fi 10 yr 3 months and -17 days):

(@dob, @today)
19270424, 19920707
19150131, 20000606
19230622, 20031215
19170124, 20020322

I cannot figure out why that is. Any ideas?

Grz,

Robert

Post #1410623

Posted Wednesday, January 23, 2013 1:33 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

r_slot (1/23/2013)

The following combinations give a negative result

What are you using?

Post #1410777

Posted Thursday, January 24, 2013 6:42 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 73, Visits: 152

SQL Server 2012

Post #1411110

Posted Thursday, January 24, 2013 7:12 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

r_slot (1/24/2013)

SQL Server 2012

What I think you were asked is what formula you are using to compute age, not what version of SQL Server.

Cool

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1411122

Posted Thursday, January 24, 2013 3:10 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:53 AM
Points: 73, Visits: 152

I am using your code of course.

Post #1411362

Posted Thursday, January 24, 2013 3:20 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 921, Visits: 3,815

r_slot (1/24/2013)

I am using your code of course.

I haven't posted it yet! w00t

w00t

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1411367

« Prev Topic | Next Topic »

Add to briefcase

26 posts, Page 2 of 3

Permissions

Expand / Collapse