0

I am converting a series of date/value pairs from one DB into another that uses offset/value pairs. The offsets are defined as the number of years, months and days since the first date. .Net appears to lack any simple way to calculate these values. I found this solution on MS's pages (and of course, can't find it now). P is a date/value pair:

Dim span As TimeSpan = P.StartDate - FirstStartDate
Dim testdate As DateTime = Date.MinValue + span
years = testdate.Year - 1 ' because MinValue is year 1
months = testdate.Month - 1
days = testdate.Day - 1

The idea is that you create a date by adding the TimeSpan to another date - in this case MinValue, and then ask the resulting date for it's year/month/day. So for instance, if the span is a year, the resulting date will be 0002/1/1 and then the result of the calculations will be 1/0/0.

So here's the problem: let's say you have a series of dates like this:

2019/1/1
2020/1/1
2023/1/1
2026/1/1

What happens is that the third entry is four years after the start date, so it calculates that this is in year 0004. And year 0004 is a leap year. But 2023 is not. So the calculation of the span from the start date includes a leap year that should not be there and it is off by one day.

There are dozens of threads about this sort of calculation here on SO, but looking over the answers they all have caveats where they don't work in one place or another, and leap years are a common cause of issues. Before I just start hacking this, is there a canonical solution to this problem that does not require importing the VisualBasic (or other) library?

Maury Markowitz
  • 9,082
  • 11
  • 46
  • 98
  • You will probably get more/better answers if you tag this with `vba` or `vb.net`. – BurnsBA Jan 20 '22 at 17:16
  • Good idea, I thought I had selected that but I guess the JS knew better. – Maury Markowitz Jan 20 '22 at 18:32
  • 1
    @MauryMarkowitz It is not clear what data you have and what you are trying to accomplish. – dbasnett Jan 20 '22 at 19:12
  • 2
    Is your problem that you're getting 1 year when you actually want 365 days (so that one of your results will be December 31 instead of January 1)? The fundamental issue is, I think, that the presence of leap days renders time differences across a leap day ambiguous, so the library has to guess what the user is most likely to want. – Craig Jan 20 '22 at 19:29
  • 1
    @MauryMarkowitz - It would be great if you could show some sample data, the calculations, and then the results entirely as valid VB.NET code. Right now you're showing partial code and you're describing the inputs and outputs and that's just not clear to me. – Enigmativity Jan 20 '22 at 23:18

1 Answers1

0

Going out on a limb here, but I think this is due to the fact that TimeSpan only understands days as its largest unit. So the difference between the same date (e.g. 1st May) in two different years will either be 365 days or 366 if it happens to cover a leap year. So that's what throws your subsequent calculations off. This is a hard problem to generalise.

Your problem statement is ambiguous. For example, what is the difference between 2021-12-01 and 2022-01-01? Is it 31 days or one month? The answer depends on what you intend to use it for.

You may have to manually calculate the difference in years, months and days then apply them using AddYears, AddMonths and AddDays, according to your own requirements.

batwad
  • 3,588
  • 1
  • 24
  • 38