# Datediff Help me understand

Should this code not retur 0? It’s returning 1 like there is a year in difference.

It’s true if you only compare the year but i thought it would compare the whole date?

The above should return 2 and it does, so how can month and day roll over newyear but interval year cannot.

Can some one direct me to information on how datediff and interval works? Because it clearly don’t work like i thought it did!

1 Like

These are all VB.net methods so Google “vb.net datediff” and…

These are extraneous. Just do Now.AddMonths(-1) and Now

You can also just use strings to designate the interval so “yyyy” instead of DateInterval.Month

Anyway, it does seem like it should return 0 for the first expression. I tested on my system and it also returns 1 and I can’t understand why. A full year has not passed since 2 months ago.

The reason it is saying 1 is that the difference between the year now (2022) and the year as it was 2 months ago (2021) is 1

It’s not telling you there has been an entire year passed, but that the date two months ago has a year with a difference of 1 to the current year.

I hope this helps explain

we do wish the same,
there is a relationship to the calendars as we can see that a resulting timestamp from a date calculation will end on TotalDays as maximum unit.

In some cases we can work with a minimal difference (e.g. when Leap Years are involved) and define that a full year is of 365 days:

And also to notice:

If you take two dates that is more than 12 months apart lets say 14 and you use dates or the fictive ones in my first examples, it will tell you 14 if interval is months so when doing months it’s actually doing calculation between the two dates using the full date, where if you do year, it’s only using the year and it makes no sense in my opinion, that day and month in interval uses the full date to return an interval rounded up or down and year doesn’t, it’s only using the year part of the date.

1 Like

I agree it may be not exactly how we think about it, but its the same mechanism in each case.

Framing it slightly differently, imagine in your 14 month example, you laid out all the months in order, starting with the month 14 months ago and ending with this month, then you gave each month a linear numeric value - so the month 14 months ago would be 1, 13 months ago would be 2 etc. the difference between the value associated with the first month and the last one would be 14.

But it’s not comparing just years, it’s comparing DATETIMEs. The difference between today and 2 months ago is not 1 year. DATEDIFF, from the way I’ve always understood it, counts full intervals. It behaves this way for other intervals.

Correct, and that difference is because it’s only counting FULL months that have passed. The partial month we’re in doesn’t count.

So why does a partial year count? It shouldn’t.

To my knowledge it works the same with the month.

its not a whole month away, but the month difference between today and 14 days ago (december) is 1

Well damn, maybe I’ve always understood it backwards. Could it be that it acts differently in vb.net vs say T-SQL?

1 Like

Dont get me wrong @postwick, i totally get where you are coming from, logically we would think of it as a whole month/year etc, but in reality we are overcomplicating what the datediff function is actually doing.

Its fairly basic in its functionality in that it takes the two dates, then ignores all other parts other than the interval type (month year etc) and then minuses those two figures.

Going back to the original example, if you went forward 2 months instead of back, the answer would be 0 years diff, and exactly the same answer if you went forward 11 months, as in all those dates the year is still going to be 2022

Just adding to this, if you did want to genuinely find out whether an entire year has passed, you could do a datediff, using days as the interval, then divide the answer by 365. That’s much more in line with how we as humans would think of it

1 Like

What you’re saying about it only using the interval part of the dates makes sense. I always thought the interval was telling it what to return but it used the full dates for comparison. I keep thinking maybe it worked differently when I first learned it long ago in probably a different language.

As you can see here no matter how i mix the interval and addmonth/days it will calculate it

In th above not only are we going more than 12 months back we are also rolling over new year

i get that i won’t get a full month and it will round up if 14 days or more into a month and round down if below and i fully get that. this is also why i expect when setting two dates even tho it rolling over new year that it would report a full year before at least 6 minths had passed (even tho it is wrong)

But i get it now and i’ve changed my code accordingly

Thx for all the input every one