How to calculate age using the ID NO

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

All Forums

General SQL Server Forums

New to SQL Server Programming

How to calculate age using the ID NO

Author

Topic

Nomandla
Starting Member

2 Posts

Posted - 2008-01-04 : 05:38:48

I have the table with list of 1500 employees with the following headings.
Surname, Ini, title, gender, Race, IdNo, FirstName

Then I need to calculate the age of each employee but I am stuk.
Please assist me.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-04 : 05:41:56

but you don't have a date of birth?

Em

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-04 : 06:06:59

As mentioned, if you had a datetime value for birth date, then this would not be a problem
[CODE]
SELECT Surname
, DateDiff(yyyy, birth_date, GetDate()) As [Age]
FROM myTable
[/CODE]
There are more accurate ways of calculating age, this only calculates the difference in years...
But let's take this one step at a time ;)

George
<3Engaged!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 07:40:20

For accuracy you need http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 08:02:45

Ahhhhh.... South African ID No's use the first 6 digits for the persons birth date ie yymmdd.
so it could be done like this:

SELECT Surname
, DateDiff(yyyy, cast(left(idno, 6) as datetime), GetDate()) As [Age]
FROM myTable

But..... Having said that, you really do need a DOB field - if your company has foreign employees or if some of them don't have id books (cause face it we have a really pathetic home affairs department here - it took my wife 2 years 2 get her new id book after we got married) then you have nothing to work with for these employees.

Duane.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-04 : 11:19:32

If you have the date of birth, you can use these functions to calculate age.

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

CODO ERGO SUM

Nomandla
Starting Member

2 Posts

Posted - 2008-01-07 : 04:52:37

Hi to all
Mhm that was very helpfull. It worked but people with ID nos starting with year 49, 48, 47 and backward their age is appearing with minus signed. e.g ID Number starting with 49 that is 1949 gives -41 of which that person is suppose to have age 59. what might be the problem.

Thanks
Nomandla

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-07 : 04:58:38

it is because to perform the calculation properly you need a 4-digit year and not a 2 digit year.

You really need a Date Of Birth field in your database - the way you have it at the moment can cause you problems.
A "Quick Fix" for now would be to create a table containing a DateOfBirth based on what you have from there ID No's, but you need to access their DOB data in future.

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-07 : 05:01:06

SELECT Surname,
DateDiff(year, cast(left(case when left(idno, 2) < '20' THEN '20' else '19' end + idno, 8) as datetime), current_timestamp) AS [Age]
FROM myTable

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources