Age calculation - 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 2005 Forums

Transact-SQL (2005)

Age calculation

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-25 : 05:16:31

Hi,

I want to claculate age say for eg.

Tab1
-------
contribution_date Dob AgeAtContribution
15-12-2005 01-07-1977 ?
17-01-2001 21-08-1981 ?
. . .
. . .
. . .

Want to calculate age at the time of contribution_date with the help of dob column above.

Thanks,
aak

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 05:23:06

select datediff(yy,'07/01/1977' ,'12/15/2005')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 05:26:33

So wrong!
select datediff(yy, '01/01/1977', '12/31/1977') -- returns 0

See here for a function that works
http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 06:14:20

Try this once,

declare @startdate varchar(32),@enddate varchar(32)
select @enddate = '12-15-2005', @startdate = '07-01-1977'
declare @year int
select @year = count(dateadd(yy,number,@startdate))
from master..spt_Values
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate

select convert(varchar(32),@year)+' yrs '+convert(varchar(100),Months,101) +' Months' AS 'Age' from
(select datediff(mm,max(years),@enddate) as 'Months' from
(select dateadd(yy,number,@startdate) as 'years'
from master..spt_Values
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate ) t ) t1

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2009-02-25 : 06:15:54

hi Peso,
select datediff(yy,'07/01/1977' ,'12/15/2005')
is giving result 28

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:18:01

Try this too, nageswar9.

declare @startdate varchar(32),@enddate varchar(32)
select @enddate = '02-24-2009', @startdate = '12-01-2000'
declare @year int
select @year = count(dateadd(yy,number,@startdate))
from master..spt_Values 
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate

select convert(varchar(32),@year)+' yrs '+convert(varchar(100),Months,101) +' Months' AS 'Age' from 
(select datediff(mm,max(years),@enddate) as 'Months' from 
(select dateadd(yy,number,@startdate) as 'years'
from master..spt_Values 
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate ) t ) t1

It counts one year wrong.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:20:29

quote:
Originally posted by nishita_s

hi Peso,
select datediff(yy,'07/01/1977' ,'12/15/2005')
is giving result 28

Yes, and it works because then month and day in the beginning year already has passed in the ending year.
Try this

select datediff(yy, '12/01/1977', '03/15/2005')

it also reports 28 year, which is not true. It is 27 years, 3 months and 2 weeks.

All of you, have a read at this article which explains how DATEDIFF works.
http://www.sqlteam.com/article/datediff-function-demystified

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 06:22:33

quote:

Originally posted by Peso

Try this too, nageswar9.

declare @startdate varchar(32),@enddate varchar(32)
select @enddate = '02-24-2009', @startdate = '12-01-2000'
declare @year int
select @year = count(dateadd(yy,number,@startdate))
from master..spt_Values 
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate

select convert(varchar(32),@year)+' yrs '+convert(varchar(100),Months,101) +' Months' AS 'Age' from 
(select datediff(mm,max(years),@enddate) as 'Months' from 
(select dateadd(yy,number,@startdate) as 'years'
from master..spt_Values 
where type ='p'
and dateadd(yy,number,@startdate) <= @enddate ) t ) t1

It counts one year wrong.

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

Ok,peso Thanks for ur help

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-25 : 06:32:54

Thank you all,

select contribution_date,
dob,
datediff(yy,dob,getdate()) current_age,
datediff(yy,dob,getdate()) - datediff(yy,contribution_date,getdate()) age_at_contribution
from
tables...

this one is also working...

Regards,
Aak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:41:15

Lets' see...

DECLARE	@Sample TABLE
	(
		contribution_date datetime,
		Dob datetime
	)

set dateformat dmy

INSERT	@Sample
SELECT	'15-12-2005', '01-07-1977' UNION ALL
SELECT	'17-01-2001', '21-08-1981'

select	contribution_date,
	dob,
	datediff(yy, dob, getdate()) AS current_age,
	datediff(yy, dob, getdate()) - datediff(yy, contribution_date, getdate()) as age_at_contribution
from	@sample

contribution_date	dob		current_age	age_at_contribution
2005-12-15		1977-07-01	32		28			-- 78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,00,01,02,03,04,05 -> 28 CORRECT!
2001-01-17		1981-08-21	28		20			-- 82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,00 -> 19 oooops wroong

See my previous posts about handling years when month and day in starting date is later than month and day in ending date.

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-25 : 07:35:22

Thank you Peso.

quote:

Originally posted by Peso

Lets' see...

DECLARE	@Sample TABLE
	(
		contribution_date datetime,
		Dob datetime
	)

set dateformat dmy

INSERT	@Sample
SELECT	'15-12-2005', '01-07-1977' UNION ALL
SELECT	'17-01-2001', '21-08-1981'

select	contribution_date,
	dob,
	datediff(yy, dob, getdate()) AS current_age,
	datediff(yy, dob, getdate()) - datediff(yy, contribution_date, getdate()) as age_at_contribution
from	@sample

contribution_date	dob		current_age	age_at_contribution
2005-12-15		1977-07-01	32		28			-- 78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,00,01,02,03,04,05 -> 28 CORRECT!
2001-01-17		1981-08-21	28		20			-- 82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,00 -> 19 oooops wroong

See my previous posts about handling years when month and day in starting date is later than month and day in ending date.

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

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-02 : 00:44:34

Hi Guys,

Found this in MSDN...
http://msdn.microsoft.com/en-us/library/ms189794.aspx
Datediff (year, date1, date2) is the same as year(date1) - year(date2)
What goes wrong is when you compare 31-12-2008 with 1-1-2009. The formula above gives 1 back while you might want a 0.
To solve this you subtract the two dates and you get a result back in date time. You compare this result with the 0 date (that's 1-1-1900).

So Select datediff (year, 0 , (date1 - date2)) gives the correct answer. This method only works when date1 > date2

Tested on...
select datediff (year, 0 , (cast('2005-03-15' as datetime) - cast('1977-12-01' as datetime)))
Returns 27...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-02 : 06:30:23

Try these too...

select datediff (year, 0 , (cast('2001-08-20' as datetime) - cast('1981-08-21' as datetime)))
select datediff (year, 0 , (cast('2009-08-20' as datetime) - cast('2007-08-21' as datetime)))

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-02 : 07:38:34

select datediff (year, 0 , (cast('2009-08-20' as datetime) - cast('2007-08-21' as datetime)))

This should give 1 year, but it's giving 2 year

quote:
Originally posted by Peso

Try these too...

select datediff (year, 0 , (cast('2001-08-20' as datetime) - cast('1981-08-21' as datetime)))
select datediff (year, 0 , (cast('2009-08-20' as datetime) - cast('2007-08-21' as datetime)))

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