Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculation between Numbers and Dates

Status
Not open for further replies.

Rbp1226

Technical User
Feb 22, 2008
21
US
Using CR 11 & Oracle 10g

I have a report need calculations between Numbers and Dates, I could not get correct result, is any one can help? Thank you!!

Simple example below:

Column 1 Column 2 Column 3
200801 01/23/1948 Mike
200801 5/27/1959 Bob

The result should be Mike = 60 years old, Bob = 49 years old

Rbp
 
Bob isn't 49 yet, won't be until 5/27. Please explain.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

"What version of URGENT!!! are you using?
 
Hello Dgillz,

You are right, Bob isn't 49 yet, I am still have no good idea for the caculation? Thanks!!

Rbp
 
Hi dgillz
I am using CRXI Release 1

Thank You!!

Rbp
 
Well you need to convert 200801 to a date. What date is this supposed to be?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

"What version of URGENT!!! are you using?
 
Once you determine how to express 200801 as a date, try a formula like this:

if Month({Birthdate}) < Month(CurrentDate) or
(Month({Birthdate)) = month(CurrentDate) and
Day({Birthdate)) <= Day(CurrentDate)))
Then Year(CurrentDate) - Year({Birthdate})
Else Year(CurrentDate) - Year({Birthdate}) - 1

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

"What version of URGENT!!! are you using?
 
Hi dgillz,

This is monthly report, yes, 200801 need convert number to date, DOB is date type. Thanks.

Rbp
 
So what date does 200801 represent?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

"What version of URGENT!!! are you using?
 
You need to clarify how the birthdate should relate to the month. With your example 200801, what if someone's birthday was during January? In other words, would you want in this example to use January 1 or January 31 to determine the age?

-LB
 
I'm certain that the following should fix your issue. However, you do need to decide on the issue raised by lbass.
Code:
datediff('yyyy',{Birthdate}, cdate(tonumber(left({Column1},4)),tonumber(mid({Column1},5,2)),XX))
{Column1} refering to Column1 as mentioned in your first post
XX the number as raised by lbass
 
Well, no, beltmanjr, that wouldn't work, regardless of the value of "XX", since datediff simply subtracts the years in the dates. You would have to use an age formula like Ken Hamady's:

WhileReadingRecords;
DateVar Birth:= {ages.Birth}; // Replace this with your field for Date Of Birth
DateVar Ann := {ages.DateAnn}; // Replace this with CurrentDate to get their age as of the time of the report
//or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

-LB
 
Thank you all,

This is monthly report, 200801 express January 31, 2008 data, and 200802 express February 28(9), 2008 data…and so on. First I need to convert this number to date, and then subtract DOB to get age. I am still trying to work out.

Thank you!

Rbp
 
Use this, substituting your actual field names:

WhileReadingRecords;
DateVar Birth:= {table.dob}; // Replace this with your field for Date Of Birth
DateVar Ann := dateserial(val(left(totext({table.yrmo},0,""),4)),val(right(totext({table.yrmo},0,""),2))+1,1)-1;//gives last day of month
//or the date field of an event to get their age as of the time of that event.
if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1

-LB
 
lbass said:
Well, no, beltmanjr, that wouldn't work, regardless of the value of "XX", since datediff simply subtracts the years in the dates. You would have to use an age formula like Ken Hamady's:
Ah, you're 100% correct! The datediff simply takes the 2 year parts and subtracts.
 
Hi LB,

The formula works well, Thank you so much! Thank you all your help guys!

Rbp
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top