DateDiff Function Over a year

[jbhsuman]

Using CR9

I am using the DateDiff function to calculate the number of days between dates as in the formula below.

 dateDiff("d", Date1, Date1)

Everything works fine until I reach a date over 1 year. When this happens I get a null return value. Can anyone shead some light as to why this is happening?

Thanks

Joe

 

[dgillz]

I just tested this and it works fine in v8.5

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

http://www.trianglepartners.com

 

[jbhsuman]

Actually as I tested more dates it is a little more confusing. It appears that this problem only happens on one record through out the report. The dates in question are:

5/15/2003 to 5/11/2004

I have other dates that fall between similar dates in May of 2003 but this problem only exists when trying to calculate # days between 5/15/2003 to 5/11/2004.

I have also tried forcing the date in the formula ie:

DateDiff("Y", date(2003,05,15), Date(2004,05,11)

and this does return the correct value. I'm thinking it has to do with one of the date values I'm using but what doesn't make sense is that everything works with all other records in the report except this one.

It's driving me crazy!

Thanks
Joe

 

[dgillz]

What type of database is this? Can you look at the raw, unformatted data in question?

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

http://www.trianglepartners.com

 

[dperk001]

Are you having problems with every date that goes over a year? or just one that is over a year but all the rest are ok that go over a year?
Are you getting back an negitive #?
I was having problems with minutes over a day and solved my problem by making two datediffs.

If you are gettin a negitive # you will need to make 2 DateDiff formulas
one you can call @yeardiff and you will use @yeardiff in the next formula @totaldaydiff
//(@yeardiff) = 1st sub formula
DateDiff ("Y",{Date1},{Date2})

//(@totaldaydiff)= 2nd and final formula that will
//accually use in your report. Depending on your Database
// you may have to add or substract.

//(@totaldaydiff)
(({@yeardiff} * 352) + DateDiff("d",{Date1},{date2})

 

[jbhsuman]

Thank you both for responding.

Dgillz, yes I can look at the raw data and that appears to be what I need to do. After carefully checking all the other values, it seems only isolated to this one record. I also tried trouble shooting by forcing different values and it appears that on this particular record there is something wrong with the date value.

Thanks for helping.

Joe

 

[Madawc]

Could one of the dates be null?

Madawc Williams
East Anglia, Great Britain

 

[dgillz]

If you look at the raw data, what tool are you using to do this? Also you never answered my question about what type of database this is.

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

http://www.trianglepartners.com

 

[jbhsuman]

Madawc, That did appear to be the problem One of the values were null and this is what caused the formula to fail only on this record.

Dgillz, Sorry, the database I am using is called Advantage and they provide a tool called data architech. Are you familiar with this backend?

Thanks again for everyone's help.

Joe

 

[chelseatech]

Hi everyone,

I can't remember every option in DateDiff but I think you want to use...
dateDiff("y", Date1, Date1)

Which gives you the number of days...
dateDiff("yyyy", Date1, Date1) gives you the number of years, but interestingly enought not an accurate "Age". You need a more complex formula to calculate age

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[jbhsuman]

Hello Chelseatech,

Thank you for your insight. I have, infact, changed my formula to use the "Y" to the "D" parameter and did not actually see any difference. Would you please explain what is the difference between using the "Y" rather than "D".

Thanks in advance.