Doughts in DateDiff

Hi Everybody
when write a sql like this i will get a result like= 3
select datediff(Year,'20041213','20070213')

but when it comes to practical side ,
if the person a person who was born in = 20041213
But the person is actually lived up to 20070213 is nearly 2 years and 3 months ,
so the actual years he live so far is 2 years ! not 3 Years

How to get this results .like i need to get actual years he live so far ?

regards
suis

Hi suis,

There is an article about calculating age of a person at http://www.kodyaz.com/articles/calculate-age-sql-code.aspx including a sql user defined function that you can use in your applications.

I'm also adding here the code for the UDF.

It takes 2 parameters; one for the date of birth and the other is the date which we will calculate the years due to

Code Snippet

CREATE FUNCTION dbo.fn_CalculateAge
(
@.BirthDate datetime,
@.CurrentDate datetime
)
RETURNS int

AS

BEGIN

IF @.BirthDate > @.CurrentDate
RETURN 0

DECLARE @.Age int
SELECT @.Age = DATEDIFF(YY, @.BirthDate, @.CurrentDate) - CASE WHEN( (MONTH(@.BirthDate)*100 + DAY(@.BirthDate)) > (MONTH(@.CurrentDate)*100 + DAY(@.CurrentDate)) ) THEN 1 ELSE 0 END
RETURN @.Age

END

GO

I hope it helps,

Eralper

|||

For BOL:

DATEDIFF returns the number of date and time boundaries crossed between two specified dates. In the following example, the first statement returns 1 year. (That is, 2006 - 2005 = 1) The second statement returns 1 month. (That is, Jan 2006 - Dec 2005) The third statement returns 12 as the number of days between the two dates.

SELECT DATEDIFF(year, '20051220', '20060101') SELECT DATEDIFF(month, '20051220', '20060101') SELECT DATEDIFF(day, '20051220', '20060101')|||Hi thanks very much for the help
These comments helped me lot to sort out my problem
thnaks again
regards
suis