How datetimediff is calculated at the backend

[gin_nguyen]

I have an issue with datetimediff function. I already found a solution but I guess the way this formula performs is not very rational. So i have a very simple example:

if I take datetimediff([start date],[end date], "%months") and my [start date] = 12/30/2020 , [end date] = 12/29/2020 . It will return 11 months but not 12 months. I mean it is only 1 day difference but why it rounds back to 11 months? I just want to understand the rationale of this rounding backward.... I already find a solution for this issue.

Thank you

[fmvizcaino]

Hi @gin_nguyen ,

The correct way of using the 3rd parameter in this function is "months".

datetimediff([start date],[end date], "months")

In this case, you will get 0 as the result.

I tried to replicate using "%months" but wasn't able to get the same answer as you, in my case the result was null as expected.

Test attached.

Best,

Fernando Vizcaino

[gin_nguyen]

Hi, so sorry, this is the formula I use:

DateTimeDiff([Start Date],[End Date],"months"). But I mean, it should return 12 months for the example above right? why it returns 11 months? that is what I want to understand.

Thank you

[gin_nguyen]

@fmvizcaino 

I attached an example for you to illustrate. I think it is not rational if this formula returns 11 months but not 12 months just because of 1 day difference. I guess what I try to do here is to understand how the number of months is derived by this formula. 

[danilang]

Hi @gin_nguyen 

The DataTimeDiff function returns the number of complete periods between the inputs.  Between 2021-01-01 00:00:00 to 2021-12-31 00:00:00, there are only 364 complete days and not 365.  From 2021-01-01 00:00:00 to 2022-01-01 00:00:00  there are 365.  With this logic, you can do things like the "11 months and 30 days" in your example.  If it rounded the months to 12, the answer would be "12 months and 30 days" which isn't correct.

Dan

[gin_nguyen]

@danilang uhm, I see your point here. but as I declare in the formula to get "months" but not month and day. if it gives me 11 month because of 1 day missing then I think the formula is quite mislead and doesn't perform its purpose here.

I think perhaps it is something can be improved. For me, i am using manually divide from days to get the full months. But i hope the formula can be clearer.

Thank you for your explanation

[danilang · Accepted solution]

Hi @gin_nguyen 

A date field in Alteryx is always assumed to at 00:00:00 time.   Between 2021-01-01 00:00:00 to 2021-12-31 23:59:59.9999... there are 12 months, but since you're checking from 2021-01-01 00:00:00 to 2021-12-31 00:00:00 there are only 11 complete months

This is also the same answer given by the DATEDIF() function in Excel

Dan  

[docadams]

I wish the example in the formula showed the quotation marks in "months" or "days".  I would have saved me a bit of time.

DateTimeDiff(dt1,dt2,u)