Decimal issue in age function 3

[Whippingboy2004]

I'm having a problem with my age function returning a decimal point with either one or two trailing zeroes. I've tried using LEFT, but since my ages are being returned as
either single or double digits, it's not been successful.

What I'm trying to accomplish with this is to get the age in months when less than 1 year and age in years otherwise. Each value is followed by 'M' or 'Y' to indicate the units. I'm currently working in SQL 2000.

ALTER FUNCTION [dbo].[FX_GetAgeInYearsOnBirthday] (@Birthdate datetime, @ServiceDate datetime)
RETURNS varchar(4) AS
BEGIN

DECLARE @Age varchar(4)
select @age =
CASE
WHEN ((datepart(year,@Birthdate) = datepart(year,@ServiceDate)))
-- AND (datepart(day,@Birthdate) = datepart(day,@ServiceDate)))
THEN CAST(datediff(month, @Birthdate, @ServiceDate)AS VARCHAR)+'M'
ELSE CAST(LEFT(ROUND(datediff(day,@Birthdate, @ServiceDate)/365.2524,0,1),2)AS VARCHAR)+'Y'
END
Return(@age)
END

Thank you for your help. I really appreciate any input you can offer.

Thanks,
T.

 

[hmckillop]

ALTER FUNCTION [dbo].[FX_GetAgeInYearsOnBirthday] (@Birthdate datetime, @ServiceDate datetime)  
RETURNS varchar(4) AS  
BEGIN 

DECLARE @Age varchar(4)
select @age =
    CASE
        WHEN ((datepart(year,@Birthdate) = datepart(year,@ServiceDate)))
--         AND (datepart(day,@Birthdate) = datepart(day,@ServiceDate)))
        THEN CAST(datediff(month, @Birthdate, @ServiceDate)AS VARCHAR)+'M' 
        ELSE CAST(datediff(year,@Birthdate, @ServiceDate)AS VARCHAR)+'Y'
        END
Return(@age)
END

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[hmckillop]

sorry forgot about the flaw in logic around the end of year - i.e. if you are born in december - the above function will return 0yr

ALTER FUNCTION [dbo].[FX_GetAgeInYearsOnBirthday] (@Birthdate datetime, @ServiceDate datetime)  
RETURNS varchar(4) AS  
BEGIN 

DECLARE @Age varchar(4)
select @age =
    CASE
        WHEN (Datediff(month,@birthdate, @servicedate ) < 12)
        THEN CAST(datediff(month, @Birthdate, @ServiceDate)AS VARCHAR)+'M' 
        ELSE CAST(datediff(year,@Birthdate, @ServiceDate)AS VARCHAR)+'Y'
        END
Return(@age)
END

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[hmckillop]

I should really test my own code before posting it!!

ALTER FUNCTION [dbo].[FX_GetAgeInYearsOnBirthday] (@Birthdate datetime, @ServiceDate datetime)  
RETURNS varchar(4) AS  
BEGIN 

DECLARE @Age varchar(4)
select @age =
    CASE
        WHEN (Datediff(month,@birthdate, @servicedate ) < 12)
        THEN CAST(datediff(month, @Birthdate, @ServiceDate)AS VARCHAR)+'M' 
        ELSE CAST(ROUND(datediff(month,@Birthdate, @ServiceDate)/12, 0, 1) AS VARCHAR)+'Y'
        END
Return(@age)
END

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[Whippingboy2004]

Thank you for your responses, however I have a question about leap years, which is represented by the 365.2524 in my original equation. Dividing by 12 doesn't quite cover them. Any thoughts?

 

[ESquared]

SELECT Case WHEN Mos < 12 THEN Convert(varchar,Mos) + 'M' ELSE Convert(varchar, Floor(Mos / 12)) + 'Y' END
	FROM (
		SELECT Mos = DateDiff(m,Convert(varchar,Year(@BirthDate))+'/' + Convert(varchar,Month(@BirthDate))+ '/1',
		DateAdd(d,DateDiff(d,@BirthDate,Convert(varchar,Year(@BirthDate))+'/' + Convert(varchar,Month(@BirthDate))+ '/1'),@ServiceDate))
	) AgeInMos

 

[ESquared]

Oops, my function is broken in one special situation: Anyone born on January 31, isn't technically one month old until March 1. Blast Feb for having 28 days.

In my method, subtracting 30 days from March 1 (Jan 31 minus Jan 1) gives you Jan 31 which looks like 0 months old.

Everything else should be okay, though. So now, how do I fix it. Blah.

 

[donutman]

DECLARE @b as datetime
DECLARE @s as datetime
SET @s='02/28/1948'
SET @b='01/31/1948'

SELECT CASE 
          When DateAdd(yy,1,@b) > @s  and 
             DateAdd(mm,DateDiff(mm,@b,@s),@b)>@s Then 
                Cast(DateDiff(mm,@b,@s)-1 as varchar) + 'M'
          When DateAdd(yy,1,@b) > @S  and 
             DateAdd(mm,DateDiff(mm,@b,@s),@b)<=@s Then
                Cast(DateDiff(mm,@b,@s) as varchar) + 'M'
          When dateadd(yy,1,@b) <= @S  and 
             DateAdd(yy,DateDiff(yy,@b,@s),@b)>@s Then
                Cast(DateDiff(yy,@b,@s)-1 as varchar) + 'Y'
          When DateAdd(yy,1,@b) <= @S  and 
             DateAdd(yy,DateDiff(yy,@b,@s),@b)<=@s Then
                Cast(DateDiff(yy,@b,@s) as varchar) + 'Y'
       END

Same result as E², so it's just a cleaner look. However, I would argue that the anomaly that you refer to is NOT a true anomaly. There is no clear defination of what it means to be 1 month old. It's a similar problem for those born on a leap year. When are they one, Feb. 28 of the next year or Mar. 1? I think the results that we get are a reasonable interpretation of what it means to be n months old. BTW, in the insurance industry age is usually defined in one of three ways
[li] your age on your last birthday[/li]
[li] your age computed as of the nearest birthday[/li]
[li] your age that you will be on your next birthday[/li]
That, of course, only begs the question.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I think of age as completion:

- You are one month older when you've arrived on the same day of the month as the starting date. If no such day exists, then the next calendar day qualifies. (Jan 31 to Feb 28 = 0 months old, Mar 1 = 1 month old.)

- You are one year older when you've arrived on the same month and day as the starting date. If no such day exists, then the next calendar day qualifies. (Feb 29 2004 to Feb 28 2004 = 0 years old, Mar 1 2004 = 1 year old.)

You have insurance industry experience that I don't in how age is 'officially' calculated... I was operating based on how I have always figured age 'should' be calculated according to common sense.

 

[ESquared]

Read, re-read, check, and re-check. My new motto before posting.

(Feb 29 2004 to Feb 28 [red]2005[/red] = 0 years old, Mar 1 [red]2005[/red] = 1 year old.)


That is to say,

- From Jan 31 you haven't completed a month on Feb 28.
- From Feb 29 2004 you haven't completed a year on Feb 28 2005.

 

[donutman]

 

[donutman]

Of course, we could still argue about when the new millennium should have started or when you should open your Christmas presents, but it is customary to celebrate your 1st birthday after you've completed your first year not on the day that you will complete it. (Ok, technically they are usually the same day...but all the more reason!)
The more interesting question is when should the 1st child born on Mars celebrate his birthday while at home in Chicago? At some time we are going to have to worry about distance. hehe
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

The 1st year is completed at either midnight of the morning of the same date, or at the same time as you were born. I figure that since time of birth is never given in official documents, treat all births as happening at midnight the morning of. So that's why whatever the "next day" is after the last one... be it Feb 29 or Mar 1.

Good point about the time versus location thing...

Moreover, will his birthday be based on a new Martian year based on the orbit of Mars?