FiveTech Support Forums

is there a function to determine the number of days between dates. For example 04/15/2010 - 03/16/2010 is 29 days. How can I determine this in a function. date format will be 04/15/2010, 03/15/2010.

Thanks for the help.

Harvey ,

This is a sample of a function i use,

HTH

hag wrote:is there a function to determine the number of days between dates. For example 04/15/2010 - 03/16/2010 is 29 days. How can I determine this in a function. date format will be 04/15/2010, 03/15/2010.

Thanks for the help.

You can simply use the minus operator.

EMG

Expanding on EMGs answer

xBase date variables can be treated as integers and basic integer operations applied to them. They are the number of days since an arbitrary start up date. (Please note this is not the format in which they are stored in .dbf files)

The number of days between date1 and date2 (inclusive) is date2 - date1 + 1

A common programming error is to forget to add the extra 1 (that is the interval between 10th March and 11th March inclusive in the same year is 2 days not 1 day).

The other potential error is that the above assumes that date2 is after date1. Robust code requires that the possibility of the dates being reversed is handled correctly. That may vary from situation to situation. If you just want the interval then ABS( date2 - date1 ) + 1 would be the better code. In other cases you may want to simply flag an error.

There are other "tricks" that flow from date variables behaving as integers.

tomorrow is DATE() + 1
this day next week is DATE() + 7
yesterday is DATE() - 1
etc

But please be careful
this day next year is not always DATE() + 365 because it may be DATE() + 366
Some years ago I came across a piece of "problem code" that was supposed to calculate a person's age in years. This code had been reported as inaccurate around a person's birthday and the code modified and retested several times but the fault persisted, at least in part. The basic approach (it wasn't actually in xBase) was like this

nAgeInYears = ( dAgeAtDate - dBirthDate ) / 365

which is doomed to failure because of the existence of leap years. Attempted modifications included adding a small fractional day, adding an INT() function etc. (They hadn't actually tried dividing by 365.25 but that still does not always yield the correct answer.) It's not a hard function to write, it is just that you can't do the calculation reliably by day interval arithmetic.

Happy programming
xProgrammer

Expanding.....

If you want to use 360 days in a year, subtraction to dates is a bit tricky also.

I use this function for it. (It is same the Date360 function in excel)

Thanks to all. It is working. And I have learned a lot.

Thanks again.

xProgrammer,

The number of days between date1 and date2 (inclusive) is date2 - date1 + 1

A common programming error is to forget to add the extra 1 (that is the interval between 10th March and 11th March inclusive in the same year is 2 days not 1 day).

OK, this may be true when you specify "inclusive," but I am having a hard time thinking of a situation where this would be used. Certainly if you want to know what date is 2 days from March 10, 2010, then you aren't going to get March 11 by adding 2.

5/10/2010 + 2 = 5/12/2010

Another way to look at it, is that there are zero days between March 10th and March 11th since they are seqential days (with no day in between).

I guess all the date calculation work I have ever done was for non-inclusive days between dates. e.g. 5/11/2010 - 5/10/2010 = 1

So, when do you use inclusive?

James

Hi James

Nice to discuss coding again.

I have had to use inclusive dates often, especially in things like tax calculations. It applies when a day is either included or excluded and you are dealing with a period of inclusion or exclusion. If I start a job on 10 March and finish it on 11 March it has taken 2 days. March runs from 1 March to 31 March and is a period of 31 days not 30 days (= 31 - 1). If I need this as a fraction of a year for tax purposes it has to be 31/365 not 30/365. (in fact I had to trouble shoot a problem application where they even got the denominator wrong because 31/12/xxxx - 01/01/xxxx gives 364 in a non leap year!)

This corresponds to similar adjustments required in analagous situations. For example an array that starts at index1 and ends at index n has n - 1 + 1 = n elements not n-1 elements. A substring from character 56 to character 60 is a string of length 60 - 56 + 1 = 5 characters.

Hotel accommodation runs differently if you use arrival date and departure date. You don't need to add the 1 day (but effectively you are only getting the latter part of the first day and the former part of the day of departure). Effectively the arrival date is inclusive and the departure date exclusive. If you used the first and last dates of night's accommodation you would need to add the extra day.

As you state if the dates are exclusive you would need to subtract a day. I haven't run across a case of day intervals with exclusive dates in any practical application I have been involved in.

My experience to date has mainly dealt with cases of inclusive dates. I guess the important point is that assuming that date2 - date1 gives the answer you need is fraught with danger - you need to check because you may need to add 1 to get the correct answer (if your dates are inclusive) or even subtract 1 day (if they are exclusive). Just adopting someone's interval function and assuming the answer it gives is correct (given your particular scenario) is dangerous.

I do try to be precise (although I often fail) which is why I specified inclusive. If our discussion has highlighted the need for careful evaluation of just what is needed when writing this type of code, then it just may have helped others avoid possible pitfalls.

Interestingly enough, in the case of calculating a person's age in years, something I alluded to in my previous post, you have a somewhat analagous situation where you may or may not need to subtract 1. If I take the current year as an integer and subtract the year of my birth as an integer then the result of that calculation is my age in years provided that it is past my birthday in the current year. If not then I have to subtract 1 from the result to get my age in years.

Of course, if I was for example 55 years old then I would be in my 56th year of life - think about it - we are in the first year of our life until we turn 1 year old whereupon we are in the second year of our life. I tried explaining that to a relation of mine but met great difficulty.

Regards
Doug (xProgrammer)

xProgrammer,

Yes, as you have pointed out, there are all kinds of ways to find date intervals.

Years ago I did a lot of interest caculations for various banks and each bank did them differently. Often it was very difficult to find someone at the bank that even knew how they did it. Usually, I had to get some sample data output from the mainframe and figure out how the did it by testing a series of formulas to find the one that gave the same figures.

I did create a formula for figuring age some time ago but i forget exactly how I did it. It is complicated and leap year does create another issue.

James

Hi James

Very interesting - many similar experiences. I wrote some software for an insurance company to illustrate a "whole of life" insurance product. I hand checked that the results of my calculations matched the supplied formulae. When I submitted my software I was informed that the calculations were incorrect because they didn't agree with the mainframe program. I admitted that they could be wrong but so could the mainframe. (In extreme cases my result was nearly twice theirs). So I asked their staistician who was responsible for the product to hand check a few examples with me. He said that that would be too difficult (I didn't agree but wasn't going to be able to convince him) so he wrote his own little program in APL to check the results. Well his results didn't agree with mine or the mainframe. I helped him debug his program and guess what - his results and mine agreed. Problem solved you might think. No! I was told that it would take at least 2 years to get the mainframe program fixed (why?). Could I modify my code to give the same (incorrect) result as the mainframe? which is what I had to do. Like you I had to get them to run some sample calculations for me on the mainframe and then figure out what it was doing.

Re calculation of age, I believe this is the simplest way (following code written as I go so not checked but I know the method is reliable even if I make a simple mistake here.)



Not affected by leap year issues and fairly simple. Your age is simply the difference between the two years provided you are past the date of birth in the current year, else it is 1 less.

Limitations:
1. Assumes that dFromDate and dToDate are passed in the correct order. Fix is easy if required but depends on what you want to happen if the order is reversed.
2. Would have a problem if either or both dates are BC
3. You may want different treatment for ages below say 1 year old. For example change function to return a string with 1-6 days, 1- 52 weeks, 1+ years.
4. You may want a fractional age or an age in years and months.
5. A way to minimise date reversal issue in some circumstances would be to have an AgeAsAtToday( dDOB ) function, which given the above could be



I would commend the above basic approach to you.