Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How datetimediff is calculated at the backend

gin_nguyen
7 - Meteor

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

7 REPLIES 7
fmvizcaino
17 - Castor
17 - Castor

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
7 - Meteor

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
7 - Meteor

@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
19 - Altair
19 - Altair

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
7 - Meteor

@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
19 - Altair
19 - Altair

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
5 - Atom

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)

Labels