How datetimediff is calculated at the backend

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

Preparation

Accepted answers

danilang

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

All comments

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

2020-04-02 How datetimediff is calculated at the backend.yxmd

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.

datetimediff test.PNG

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

Hi @gin_nguyen

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)

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335