Problem with a date difference formula

[Padre764]

Hello,

I have to calculate the difference between two dates (action date and birthday (dob)) and return the result in years, months, days. I have a formula that works really well except for two things. 1)When the day(action_date) = 31 and the month(dob) in (Feb, Apr, Jun, Sep, Nov). When I try to add the result of the formula back to the dob I get the action_date - 1. 2) There are inconsistant results month(dob) = 2.
Below is the formula.

select action_date, dob
  , case when month(action_date) > month(dob) or (month(action_date) = month(dob) and day(action_date) >= day(dob) )
		then datediff(yy, dob, action_date) 
	else datediff(yy, dob, action_date) - 1 end years
 , case when month(action_date) >= month(dob) and day(action_date) >= day(dob)
		then  month(action_date) - month(dob)
	when month(action_date) > month(dob) and day(action_date) < day(dob)
		then  month(action_date) - month(dob) - 1
        when month(action_date) = month(dob) and day(action_date) < day(dob)
		then  11
	else case when day(action_date) < day(dob)
			then 12-month(dob)+month(action_date)-1 
			else 12-month(dob)+month(action_date) end 
	 end months
 ,case when day(action_date) > day(dob) and month(dob) in (4,6,9,11) and month(action_date) in (1,3,5,7,8,10,12) and day(action_date) = 31 then 30 - day(dob)
       when day(action_date) > day(dob) then day(action_date) - day(dob)
		else 
			case when day(dob) = day(action_date) then 0
				when month(action_date) in (4,6,9,11) and month(dob) in (1,3,5,7,8,10,12)then 31-day(dob) + day(action_date)
				when month(action_date) in (4,6,9,11) and month(dob) = 2 and dbo.ufn_IsLeapYear(dob) = 1 then 29-day(dob) + day(action_date)
				when month(action_date) in (4,6,9,11) and month(dob) = 2 then 28-day(dob) + day(action_date)
				when month(action_date) in (4,6,9,11) then 30-day(dob) + day(action_date)
				when month(action_date) in (1,3,5,7,8,10,12) and month(dob) in (4,6,9,11) then 30-day(dob) + day(action_date)
				when month(action_date) in (1,3,5,7,8,10,12) and month(dob) = 2 and dbo.ufn_IsLeapYear(dob) = 1 then 29 -day(dob) + day(action_date)
				when month(action_date) in (1,3,5,7,8,10,12) and month(dob) = 2 then 28 -day(dob) + day(action_date)
				when month(action_date) in (1,3,5,7,8,10,12) then 31-day(dob) + day(action_date)
				when month(action_date) = 2 and month(dob) in (4,6,9,11) then 30-day(dob) + day(action_date)
				else 31-day(dob) + day(action_date) end end 
from tbl

 

[Qik3Coder]

its five, so i'm calling it for the day, but maybe you can make use of this: NOTE it only works when all the @bdy parts are less than the GetDate parts...

declare @bDay datetime
declare @today datetime
set @today = getDate()
set @bday = '1983-05-04 15:21:00.000'
declare @years int
declare @months int
declare @days int
set @years = dateDiff(yy, @bday, @today)
set @today = dateAdd(yy, -1*@years, @today)
select @today
set @months = dateDiff(mm, @bday, @today)
set @today = dateAdd(mm, -1*@months, @today)
select @today
set @days = dateDiff(dd, @bday, @today)
Select 'It has been '+cast(@years as varchar)+' years, '+cast(@months as varchar)+' months, and '+cast(@days as varchar)+' days since your last check-up.'

-The answer to your problem may not be the answer to your question.

 

[ESquared]

Unless I am remembering wrong, there is an FAQ in this forum's FAQ section on calculating age.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]