Crystal Reports datediff for months Jan 1 to Jan 1 giving 13total

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: datediff for months Jan 1 to Jan 1 giving 13total

Author	Message
Tonyak74 Newbie Joined: 24 Apr 2013 Online Status: Offline Posts: 28	Topic: datediff for months Jan 1 to Jan 1 giving 13total Posted: 10 Dec 2013 at 11:39am
	Hi all.. I am still a newbie.... I am trying to count the total months for my contract lengths. I have used datediff('m',({Contract.StartDate}),({Contract.ExpirDate}))+1; This does count the number of months However the problem I am running into is When My contract is from Jan 1 2001 - Jan 1 2002, I am getting a count of 13. Any help would be greatly appreciated... Thanks, TK
	IP Logged


Sastry Moderator Joined: 16 Jul 2012 Online Status: Offline Posts: 537	Posted: 10 Dec 2013 at 7:09pm
	Hi May be this is a odd one, try this : truncate(datediff('d',{?dt1},{?dt2})/30)
	Thanks, Sastry
	IP Logged

Tonyak74 Newbie Joined: 24 Apr 2013 Online Status: Offline Posts: 28	Posted: 11 Dec 2013 at 5:14am
	That... Worked.... Thank you so much....
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 11 Dec 2013 at 6:49am
	the issue is the contract is ending on 1/1/2002...it probably ended at 12/31/2001 23:59:59.999 DateDiff is very literal, in that for the month/day/time frame it will count as 1 any part of span. so 1/31/01-1/1/02 would also return 13 months. Just something to be aware of...Sastry's solution is a good general rule, but I would be willing to be that there are a few time frames that will confuse it and return incorrect results as well. Again, just something to keep in mind for if/when someone complains about the report. HTH
	IP Logged

Sastry Moderator Joined: 16 Jul 2012 Online Status: Offline Posts: 537	Posted: 11 Dec 2013 at 6:33pm
	hi Yes Lockwelle, you are right It is a general solution not the accurate one. The scenario which he is trying is little odd, we can't reduce 1 day just like that and show the date difference. We may have to check normally when the contract starts and when it ends. Mostly based on this we can write date add statement. I know like if any contract starts in the middle of the month and ends in the same month then it will give zero months.
	Thanks, Sastry
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum