need help to calculate age - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

need help to calculate age

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-09 : 07:36:43

My explanation as following,

Before year 2000
1994 will label as 94
1995 will label as 95
1996 will label as 96
1997 will label as 97
1998 will label as 98
1999 will label as 99

declare @noKP as varchar(12)
set @noKP='990308085777' -- my year of date of birth is 1999

select case
		when convert(int,SUBSTRING(@noKP,0,3)) <= 99 
		then DATEPART(YEAR,GETDATE()) - Convert(int,'19' + SUBSTRING(@noKP,0,3))
		else DATEPART(YEAR,GETDATE()) - Convert(int,'20' + SUBSTRING(@noKP,0,3))
		end as myAge

After year 2000
2000 will label as 00
2001 will label as 01
2002 will label as 02
2003 will label as 03
2004 will label as 04
2005 will label as 05

declare @noKP as varchar(12)
set @noKP='010308085777' -- my year of date of birth is 2001

select case
		when convert(int,SUBSTRING(@noKP,0,3)) <= 99 
		then DATEPART(YEAR,GETDATE()) - Convert(int,'19' + SUBSTRING(@noKP,0,3))
		else DATEPART(YEAR,GETDATE()) - Convert(int,'20' + SUBSTRING(@noKP,0,3))
		end as myAge

My calculation for after 2000 got problem

Need help to fixe and tune

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-09 : 08:02:02

What is your century(19 or 20) when your converted first three positions is 70? 50? 20? 10? converting the first three to an int, they will ALWAYS be less than 99. You need a cutoff year.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-02-09 : 08:05:34

how to do that sir?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-09 : 09:09:09

You need to decide that. When you get 20, should that be 1920 or 2020? You need to decide when that cutoff should be. Then you can change your <=99 to a range, say between 50 and 99. Perhaps whoever is giving you this data can tell you how they decided to encode it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources