calculating age using datediff

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)

calculating age using datediff

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-16 : 10:15:42

CASE
WHEN @FromDate > @ToDate THEN NULL
WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
ELSE DATEDIFF(MONTH, @FromDate, @ToDate)
END / 12 --age calculation

So far the above code is working fine, will it give problem in future, any one who is known to this situation can help.

Regards,
aak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:18:42

I have updated my blog post.
See 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"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-16 : 10:30:48

Most people consider that people born on Feb 29 have their birthday on Feb 28 in non-leap years, but your code returns an age of zero for these from/to dates: 2008-02-29 2009-02-28

Function F_AGE_IN_YEARS on the link below calculates age in years from @START_DATE through @END_DATE and returns the age as an integer.
Age Function F_AGE_IN_YEARS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

CODO ERGO SUM

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-16 : 10:33:51

Thanks all

CASE
WHEN @FromDate > @ToDate THEN NULL
WHEN DATEPART(DAY, @FromDate) >= DATEPART(DAY, @ToDate) AND @ToDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) THEN DATEDIFF(MONTH, @FromDate, @ToDate)
WHEN DATEPART(DAY, @FromDate) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @FromDate, @ToDate) - 1
ELSE DATEDIFF(MONTH, @FromDate, @ToDate)
END / 12

The above is the code at Peso's Link I didnt understood the significance of getdate() function here.
coz we are passing only two dates i.e, from date and to date.

I have not included the line which contains getdate()... is it required. plz help

Regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:42:57

GETDATE should be @ToDate.
But you will need to read the link above and copy the new code.

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-16 : 10:59:21

Thanks Peso,

So you have updated the code?

so what I am using is right wrt to the below just need to change the month to year and no need to divide by 12

CASE
WHEN @ToDate < DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(YEAR, @FromDate, @ToDate) - 1

ELSE DATEDIFF(YEAR, @FromDate, @ToDate)

END

Regards,
aak

quote:
Originally posted by Peso

GETDATE should be @ToDate.
But you will need to read the link above and copy the new code.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 11:00:49

Yes.

quote:
Originally posted by Peso

I have updated my blog post.
See 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"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-03-16 : 11:24:41

Hi Peso,

The above code and the datediff result is diff for the below dates

'1941-03-04' ( yyyy-mm-dd format) '2008-06-20'

with datediff('1941-03-04','2008-06-20') I am getting 67

with the below code I am getting 66
CASE
WHEN @ToDate < DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate) THEN DATEDIFF(YEAR, @FromDate, @ToDate) - 1
ELSE DATEDIFF(YEAR, @FromDate, @ToDate)
END

Is there any issues.

Regards,
aak

quote:
Originally posted by Peso

Yes.
quote:
Originally posted by Peso

I have updated my blog post.
See 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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 15:09:31

Both give me 67

DECLARE	@d1 DATETIME,
	@d2 DATETIME

SELECT	@d1 = '19410304',
	@d2 = '20080620'

SELECT	DATEDIFF(YEAR, @d1, @d2),
	CASE
		WHEN @d2 < DATEADD(YEAR, DATEDIFF(YEAR, @d1, @d2), @d1) THEN DATEDIFF(YEAR, @d1, @d2) - 1
		ELSE DATEDIFF(YEAR, @d1, @d2)
	END

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources