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

General SQL Server Forums

New to SQL Server Programming

Calculate age

Author

Topic

1sabine8
Posting Yak Master

130 Posts

Posted - 2008-11-26 : 02:03:07

Hi,
I have a column that stores the year of birth as string. What i need to do is calculate the age of the person using an sql query. How can this be done?
Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-26 : 02:07:31

An approach:
select datepart(year,getdate()) - convert(int,'1961')

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 02:23:48

quote:
Originally posted by 1sabine8

Hi,
I have a column that stores the year of birth as string. What i need to do is calculate the age of the person using an sql query. How can this be done?
Thanks in advance

dont you have month & day values? what if month of birth is after current month or even if month is same,date comes after current day?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 02:25:53

I guess OP is only interested who has a birthday this year?

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-26 : 05:57:58

SELECT DATEDIFF(yy,yourdatecolumn,GETDATE())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 07:38:40

quote:
Originally posted by bklr

SELECT DATEDIFF(yy,yourdatecolumn,GETDATE())

this will give only approx age in years

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 07:40:34

It will return how many years the person will be current year.

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-26 : 11:01:59

One of these functions should do what you want.

This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

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

CODO ERGO SUM

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-26 : 11:19:11

A little more simplistic but it works:

SELECT CONVERT(varchar(20),DateOfBirth,101),
DATEDIFF (yyyy,DateOfBirth, GETDATE() ) - CASE WHEN (MONTH(DateOfBirth) = MONTH(GETDATE())
AND DAY(DateOfBirth) > DAY(GETDATE()) OR MONTH(DateOfBirth) > MONTH(GETDATE()))
THEN 1 ELSE 0 END AS 'Age'
FROM EmployeeFile
where DateOfBirth is not null

Terry

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-26 : 23:58:05

SELECT CONVERT(varchar(20),datecolumn,101) AS 'DateofBirth',
DATEDIFF (yyyy,datecolumn, GETDATE() ) - CASE WHEN (MONTH(datecolumn) = MONTH(GETDATE())
AND DAY(datecolumn) > DAY(GETDATE()) OR MONTH(datecolumn) > MONTH(GETDATE())) THEN 1 ELSE 0 END AS 'years' ,
DATEDIFF (m,REPLACE(datecolumn,DATEPART(yy,datecolumn),DATEPART(yy,GETDATE())), GETDATE() ) AS 'Months',
DATEDIFF (d,REPLACE(datecolumn,DATEPART(yy,datecolumn),DATEPART(yy,GETDATE())), GETDATE() ) AS 'Days'
FROM yourtable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 02:35:27

quote:
Originally posted by bklr

SELECT DATEDIFF(yy,yourdatecolumn,GETDATE())

Not reliable

SELECT DATEDIFF(yy,'2007-12-31 23:59:59:996','2008-01-01 00:00:00:000')

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 02:45:46

What's wrong with that?
Since OP only stores birthyear a person born 2007 will be 1 year old 2008.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 03:17:20

My answer is based on the date calculation shown by bklr

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 03:22:18

Aaahhh....

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-27 : 03:22:31

quote:
Originally posted by madhivanan

My answer is based on the date calculation shown by bklr

Madhivanan

Failing to plan is Planning to fail

SELECT DATEDIFF(yy,'2007-12-31 23:59:59:996','2008-01-01 00:00:00:000')

it is showing the output as 1
Wts the problem in that query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 03:29:30

quote:
Originally posted by bklr

quote:
Originally posted by madhivanan

My answer is based on the date calculation shown by bklr

Madhivanan

Failing to plan is Planning to fail

SELECT DATEDIFF(yy,'2007-12-31 23:59:59:996','2008-01-01 00:00:00:000')

it is showing the output as 1
Wts the problem in that query

How can you tell that a person's age is 1 when he/she is just .004 seconds old?
The datediff wont work properly. See MVJ's links

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-27 : 05:26:27

quote:
Originally posted by bklr

quote:
Originally posted by madhivanan

My answer is based on the date calculation shown by bklr

Madhivanan

Failing to plan is Planning to fail

SELECT DATEDIFF(yy,'2007-12-31 23:59:59:996','2008-01-01 00:00:00:000')

it is showing the output as 1
Wts the problem in that query

Have a look at datediff in bol.
It returns the number of boundaries between the two dates. So for years it will be the number of jan 1's. In this case 1.
Hence why tosscrosby subtracts 1 if the second date is earlier in the year than the first.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 05:51:36

About DATEDIFF, see http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-27 : 06:36:56

hi all,
try with this

declare @yourdob datetime
set @yourdob='1983-03-03'
select datediff(dd,@yourdob,getdate())/365
select datediff(mm,@yourdob,getdate())-datediff(dd,@yourdob,getdate())/365*12
select datediff(dd,dateadd(mm,datediff(mm,@yourdob,getdate()) -datediff(dd,@yourdob,getdate())/365*12,dateadd(yy,datediff(dd,@yourdob,getdate())/365,@yourdob)),getdate())

ok tanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources