Substract two dates to find number of years 1

[namax]

I am writing a check to ensure that the age of the participant of a clinical study is consistent with the date of birth(DOB) and the enrolment date(ENROLDATE).I tried using substr() to get the year portion of DOB and substract it from the year portion of ENROLDATE but it is not working.This is the code:
dob=10/08/1987
enroldate=03/05/2011
(substr(dtoc(enroldate),7,4)) - (substr(dtoc(dob),7,4))

Could anyone help me with this please.

Thankyou in advance.

Yanx

 

[Dan Freeman]

What natural pairing does the enrollment date have with the date of birth? And why is it even relevant to calculating age?

Age is simple date math:

? (date() - BirthDate) / 365

(With adjustments for leap years of course ... the expression can actually get a little long-winded.)

But what does enrollment date have to do with age?

Do you really mean "age at time of enrollment"? That's the same calculation, but you use enrollment date in place of date().

 

[Mike Lewis]

Yes, I was also confused about how the enrollment date relates to the date of birth.

However, regarding your code: If the two date are held as dates, as opposed to character strings, the easiest way to find the years is to use the YEAR() function.

For example, YEAR(dob) is the year of birth.

And the age at enrollment is:

CEILING(YEAR(Enroldate) - YEAR(dob))

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[dbMark]

Use Dan's example above with FLOOR() and you will get the person's age at last birthday with error of just + or - a day or so.

? FLOOR( (enroldate - dob) / 365.25 )

 

[Koen Piller]

Hi,
In case you want persons age in years you could make use

*Agecalculator
PARAMETERS tdDob, tdDor
LOCAL ldDob, lnMonthDob, lnDayDob, lnYearDob
LOCAL ldDor, lnMonthDor, lnDayDor, lnYearDor
LOCAL lnAge
* DOB = Date of Birth
* DOR = Date of Reference

ldDob = CTOD(tdDob)
lnMonthDob = MONTH(m.ldDob)
lnDayDob   = DAY(m.ldDob)
lnYearDob  = YEAR(m.ldDob)

ldDor = CTOD(tdDor)
lnMonthDor = MONTH(m.ldDor)
lnDayDor = DAY(m.ldDor)
lnYearDor = YEAR(m.ldDor)
lnAge = lnYearDor-lnYearDob - 1
IF m.lnMonthdob < m.lnMonthDor AND m.lnDaydor <= m.lnDayDob  
 lnAge = m.lnAge
ENDIF
RETURN m.lnAge

Regards,

Jockey(2)

 

[Olaf Doschke]

Calculate year difference year(reference date)-year(date of birth), then subtract 1, if GoMonth(date of birth,12*year difference) is larger than the reference date.

That is:

lnYears = year(enroldate)-year(dob)
lnAge = lnYears - iif(enroldate<GoMonth(dob,12*lnYears),1,0)

* -- Or in a single expression (eg for SQL without calling a procedure):
lnAge = year(enroldate)-year(dob)-iif(enroldate<GoMonth(dob,12*(year(enroldate)-year(dob))),1,0)

@Jockey: You must be missing something here:

IF m.lnMonthdob < m.lnMonthDor AND m.lnDaydor <= m.lnDayDob  
 lnAge = m.lnAge
ENDIF

You're not changing lnAge. Also if you did, what if enrolldate is within the same month, as birht month but before or after the birthday. checking the reference date with the birthday of the same year computed by gomonth() is easier to do.

Bye, Olaf.

 

[JRB-Bldr]

Calculate year difference year(reference date)-year(date of birth), then subtract 1

Just a bit of trivia - While the above is accurate in most places there are some cultures (e.g. Korea) where an individual is 1 year old from the date of birth, not after 12 months.

JRB-Bldr

 

[Olaf Doschke]

Thanks jrbbldr.

I didn't know, that some cultures begin counting age at 1, besides the AD (anno domini) notation of years, which indicate christs age and also don't start at 0, see

http://en.wikipedia.org/wiki/Anno_Domini#No_year_zero

The ages meaning then is 1st, 2nd, 3rd... year of life.

Some people celebrate the number 42 for some reasons (hint: douglas adams) and forget, that at age 42, their 42nd year does not begin, but has ended.

Bye, Olaf.

 

[Mike Lewis]

there are some cultures (e.g. Korea) where an individual is 1 year old from the date of birth, not after 12 months

I didn't know that. Thanks.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Koen Piller]

Olaf,

Of course you are correct, an essential line was missing in my code!
Also the if month(enrolldate) = month(date()) the calculated age is not correct.
Since I cannot figure out how gomonth() would help me in this matter I have quickly made an easy reading Do Case, cathing to my opionion all the possibilities and now giving Namax the requested years.
So Namax please change the last If/Endif of my solution with below case statement (pending Olaf's easier gomonth solution )

Do Case
Case m.lnMonthDob = m.lnMonthDor And m.lnDayDor <= m.lnDayDob
	lnAge = lnYearDor - lnYearDob
Case m.lnMonthDob < m.lnMonthDor And m.lnDayDor <= m.lnDayDob
	lnAge = lnYearDor-lnYearDob
Othe
	lnAge = lnYearDor-lnYearDob -1

Regards,

Jockey(2)

 

[Olaf Doschke]

I wouldn't say my solution is easier, in fact it's harder to read and understand, especially when written as a single expression.

Those are the expressions I sometimes make use of in SQL, as it's more portable, deceoupled, not depending on other files being present with the needed functions in them.

One more thought on your code: If m.lnMonthDob < m.lnMonthDor you won't need to check how the days compare, if the one date is in a previous month, the day of both dates don't matter anymore, the birthday of that year has not yet been then.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, the other way around: The birthday of that year HAS already been passed, if m.lnMonthDob < m.lnMonthDor, as then the reference dates month is after the birthday month.

Bye, Olaf.

 

[JCreamerII]

I work with dates a lot, and the calc has to be perfect. I subtract the years, and then test the month, and if needed test the day.

Funny thing it's not like regular digits, not base 10, so the calc's get funky when you work to EOM or to a specific point of a month, or any of the other stuff you may need to do with dates. Whether it's days, weeks, or whatever.

Jim C.

 

[DTracy]

Jockey(2)

Wouldn't you return lnAge rather than m.lnAge from your function?

Thanks
David.

 

[Olaf Doschke]

David, m.lnAge and lnAge are the same. m.lnAge just makes sure you read the memory variable, not a table/alias field of the same name.

See

http://msdn.microsoft.com/en-US/library/addt8a5f(v=VS.80).aspx

Bye, Olaf.

 

[DTracy]

From the function source code:

LOCAL lnAge
.
.
.
lnAge = lnYearDor-lnYearDob - 1 && The local var is init here.
IF m.lnMonthdob < m.lnMonthDor AND m.lnDaydor <= m.lnDayDob
lnAge = m.lnAge && The local var is updated from the memvar.
ENDIF
RETURN m.lnAge

What I'm trying to say is that lnAge was declared as a separate variable from m.lnAge and is being treated as a separate variable. There was an assumption made that they are separate vars during the If block.

I'm not a VFP programmer, just trying to learn the dialect, and this is what I see.

Thanks,
David.

 

[superpck]

Function YearCal(dob as date,Enroldate as date)
return year(Enroldate)-year(dob) - iif(Enroldate > gomonth(dob,(year(Enroldate)-year(dob))*12),0,1)

 

[Olaf Doschke]

David,

as I said both lnAge and m.lnAge are actually the same, exact the same.

You can adress lnAge as both m.lnAge and lnAge. The reason to use m (the memory variable object of vfp) is to assure you read the memory variable and not a table field of the same name accidentally.

Bye, Olaf.

 

[Koen Piller]

Namax,

Olaf made a very good point, my code was incorrect and even the correction was not o.k. - actualy you already have a solid working solution from Olaf in which he makes use of the native vfp gomonth() giving the age so normaly there is no need to repost my lousy coding, just to show-proof you "there are 1000 ways to skill a fox" here is a solution making use of the native vfp function sys(11) which determines the daynumber from a date starting at julianday 1 - January 1, 4713 BC -

*Agecalculator
Parameters tdDob, tdDor
Local ldDob, lnYearDob
Local ldDor, lnYearDor
Local lnAge


* DOB = Date of Birth
If Vartype(tdDob)<>'D'
	ldDob = Ctod(tdDob)
Else
	ldDob = tdDob
Endif
lnYearDob  = Year(m.ldDob)

* DOR = Date of Reference
If Vartype(tdDor)<>'D'
	ldDor = Ctod(tdDor)
Else
	ldDor = tdDor
Endif
lnYearDor = Year(m.ldDor)

If Sys(11,Ctod('^'+Str(m.lnYearDor)+'-'+Padl(Month(tdDob),2,'0')+'-'+Padl(Day(tdDob),2,'0')))< ;
		SYS(11,Ctod('^'+Str(m.lnYearDor)+'-'+Padl(Month(tdDor),2,'0')+'-'+Padl(Day(tdDor),2,'0')))
	lnAge = lnYearDor-lnYearDob -1
Else
	lnAge = lnYearDor-lnYearDob
Endif

Return m.lnAge

Regards,

Jockey(2)