How to calculate age

[nikol]

I'm using "datediff(yyyy,val_date,getdate()) as age"
formula to calculate the age.
If the bdate is in this year like "2005-03-09"(5 months old)
or"2005-05-21"(6 days) old.
Is it possible to calculate the age in months & days also in athe same datediff formula?

 

[checkai]

have you searched age on this forum, i have seen many good solutions here regarding the calculation of ages.

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[TJRTech]

Assuming today is 2005-07-27, than the Months and Days since the birthdate of 2005-03-09 if 4mo 18 days, right?

The following will give you that:

DECLARE @bdate as datetime
SET @bdate = '2005-03-09'

SELECT datediff(mm, @bdate, GetDate()) as AgeInMonths
SELECT datediff(dd, dateadd(mm, datediff(mm, @bdate, GetDate()), @bdate), GetDate()) as AgeInDaysSansMonth

TJR

 

[NoCoolHandle]

Try this

Alter Function DateDifference
(@Date1 as datetime,
@Date2 as datetime)
Returns  Varchar(300)
as
begin
declare @Years varchar(100), @mnts varchar(100), @days  varchar(100)

Set @years = datediff(yy,@Date1,@date2)
Set @date1 = dateadd(yy,cast(@years as int),@Date1)
set @Mnts= datediff(m,  @Date1,@date2)
Set @Date1 = Dateadd(m,cast(@mnts as int),@date1)
Set @Days= datediff(d,@Date1,@date2)

Return @Years + ' Years, ' + @mnts + ' Months, ' + @days + ' Days'
End

go
Select dbo.DateDifference('1964-05-09' , getdate())

Returns something like
'41 Years, 2 Months, 18 Days'

 

[vongrunt]

DATEDIFF() returns number of crossed interval boundaries:

set dateformat dmy
select datediff(yy, '31/12/2000', '01/01/2001')
select datediff(yy, '01/01/2000', '31/12/2001')

Therefore...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

THis one works much better..

Alter Function DateDifference
(@Date1 as datetime,
@Date2 as datetime)
Returns  Varchar(300)
as
begin
declare @Years varchar(100), @mnts varchar(100), @days  varchar(100)

Set @years = datediff(yy,@Date1,@date2)
Set @date1 = dateadd(yy,cast(@years as int),@Date1)
set @Mnts= datediff(m,  @Date1,@date2)
Set @Date1 = Dateadd(m,cast(@mnts as int),@date1)
Set @Days= datediff(d,@Date1,@date2)
If @mnts < 0 
begin
	set @years = @years - 1
       set @Mnts = 12 + @mnts
end 
go
Select dbo.DateDifference('1996-09-5' , getdate())

The last one returned the year and months wrong.

The year would be 1 greater and then month a negative. This corrects that.

 

[gmmastros]

NoCoolHandle,

I'm not sure why, but your UDF returns '4 Years, 7 Months, -3 Days' when I send it '200-12-31'.

I have a UDF that I found on the internet a very long time ago. I like your solution because there are no loops in it. The UDF I found has 2 while loops, so performance isn't so good.

ALTER   Function dbo.CalculateAge
	(
	@BirthDate As DateTime,
	@Now as DateTime
	)
	Returns VarChar(100)
AS
Begin
--dateformat= yyyymmdd
declare @from datetime 
declare @to datetime 

set @from = Convert(VarChar(4), Year(@BirthDate)) 
			+ Right('00' + Convert(VarChar(2), Month(@BirthDate)), 2)
			+ Right('00' + Convert(VarChar(2), Day(@BirthDate)), 2)

set @to = Convert(VarChar(4), Year(@Now)) 
			+ Right('00' + Convert(VarChar(2), Month(@Now)), 2)
			+ Right('00' + Convert(VarChar(2), Day(@Now)), 2)

declare @year int set @year=0
declare @month int set @month=0
declare @day int set @day=0
declare @rndate datetime     
set @rndate=@from --rndate = runningdate

--calculate the @year 
if(datepart(year,@to)>datepart(year,@from))
begin
    set @year = datediff(year,@from,@to)
    set @rndate = dateadd(year,@year,@from)    --update runningdate
    if(@rndate>@to)
    begin
        set @year = datediff(year,@from,dateadd(year,-1,@to)) --calculate years from @from to @to - 1 year
        set @rndate = dateadd(year,@year,@from)    --update runningdate
    end
    
end

--add 1 month as long as running date is smaller than or equal to @to
while @rndate<=@to
begin
    set @rndate = dateadd(month,1,@rndate)
    if (@rndate<=@to)
    begin 
        set @month=@month+1
    end
end
--set @rndate back 1 month
set @rndate=dateadd(month,-1,@rndate)

--start to count days
while @rndate<@to
begin
    set @rndate=dateadd(day,1,@rndate)
    set @day=@day+1
end

Return Convert(VarChar(5), @year) + ' Years, ' + Convert(VarChar(5), @Month) + ' Months, ' + Convert(VarChar(5), @day) + ' Days'
End

Many months ago, donutman created a select query that also calculates age. I converted it to a UDF so that it was easier to use.

ALTER  Function GetDonuts
	(
	@Date1 dateTime,
	@Date2 DateTime
	)
Returns VarChar(10)
As
Begin
RETURN	Convert(VarChar(4), CASE WHEN DateAdd(yy,DateDiff(yy,@Date1,@Date2),@Date1)>@Date2 THEN DateDiff(yy,@Date1,@Date2)-1 ELSE DateDiff(yy,@Date1,@Date2) END)
       	+ ',' + 
	Convert(VarChar(2), CASE WHEN DateAdd(mm,DateDiff(mm,@Date1,@Date2),@Date1)>@Date2 THEN (DateDiff(mm,@Date1,@Date2)-1)%12 ELSE DateDiff(mm,@Date1,@Date2)%12 END)
       	+ ',' + 
	Convert(VarChar(2), DateDiff(dd,DateAdd(mm,CASE WHEN DateAdd(mm,DateDiff(mm,@Date1,@Date2),@Date1)>@Date2 THEN (DateDiff(mm,@Date1,@Date2)-1)%12 ELSE DateDiff(mm,@Date1,@Date2)%12 END ,DateAdd(yy,CASE WHEN DateAdd(yy,DateDiff(yy,@Date1,@Date2),@Date1)>@Date2 THEN DateDiff(yy,@Date1,@Date2)-1 ELSE DateDiff(yy,@Date1,@Date2)END,@Date1)) ,@Date2))
End

What's interesting is that all 3 methods. give different results for 12/31/2000.

Your method: 4 years, 7 months, -3 days
My Method: 4 years, 7 months, 0 days
Donutmans method: 4 years, 6 months, 28 days



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NoCoolHandle]

Donuts must be correct I have obviously ignored the idea that if i subtranct x months from a date it might be - days.

Go with dounts. THis was just a kind of fun exercise for me. I probably should have spent more time testing it.


Rob

 

[NoCoolHandle]

George..


(It gets the same resutls as dountmans, but might be easier to follow - 6 of one 1/2 a dozen of the other)

Try

Alter Function DateDifference
(@Date1 as datetime,
@Date2 as datetime)
Returns  Varchar(300)
as
begin
declare @Years varchar(100), @mnts varchar(100), @days  varchar(100)

Set @years = datediff(yy,@Date1,@date2)
Set @date1 = dateadd(yy,cast(@years as int),@Date1)
set @Mnts= datediff(m,  @Date1,@date2)
Set @Date1 = Dateadd(m,cast(@mnts as int),@date1)
Set @Days= datediff(d,@Date1,@date2)
If @mnts < 0 
begin
	set @years = @years - 1
       set @Mnts = 12 + @mnts
end 
if @Days < 0 
	begin
          set @mnts = @mnts -1
		-- how many fng days were there this during the mnt in question?
		Declare @iDays int, @month int
		set @month = month(@date2)
		declare @d1 datetime
		declare @d2 datetime
		set @d1 =cast(year(@date2)as varchar(4)) + '/' + cast(month(@date2) as varchar(2)) + '/1'
		set @d2 = cast(year(@date2)as varchar(4)) + '/' + cast(month(@date2)+1 as varchar(2)) + '/1'	
	       set @days = datediff(d,@d1 ,@d2)   + @days
	end

Return @Years + ' Years, ' + @mnts + ' Months, ' + @days + ' Days'

 

[TJRTech]

Again, maybe I am missing something, but why isn't this as easy as three simple calcs:

DECLARE @bdate as datetime
SET @bdate = '03/09/2005'

SELECT datediff(yy, @bdate, GetDate()) as Years
SELECT datediff(mm, @bdate, GetDate()) % 12 as Months
SELECT datediff(dd, dateadd(mm, datediff(mm, @bdate, GetDate()), @bdate), GetDate()) as Days

 

[TJRTech]

I see why now...the -3 days issue...hhmmm, time to noodle.

TJR

 

[TJRTech]

A question to you all....

If I was born on 01/31/2000 (Jan, 31, 2000), and TODAY is February 28th, 2005 how many yy, mm and days old am I?

My kneejerk tells me that I am:

5 years, 1 month, and 0 days old

I say this because February 28th is one month after Jan 31st.

Am I correct?

Or would you guys say that I am:

5 years, 0 months and 28 days old?

I guess it all depends on how you look at it and what it means to be "a month" older.

TJR

 

[vongrunt]

Hm... I tried to explain that in faq183-5842 (tip #2) together with DATEDIFF() issue.

Either people don't read FAQs or my English is plain bad. .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

The function i wrote came back with

5 Years, 1 Months, 0 Days

All i can say is that you tupe better than me and i am much older!

 

[NoCoolHandle]

VOn...

First I can't read
Sedon I can't type

Third - I just love to bang my head agains a wall. Lessons are learned better when they hurt