How to calculate Months between two dates with DATEDIFF?

b_rad

I was trying to use the formula DATEDIFF(month,date1,date2) to calculate the months between two dates based on the function details that shows up in Magic ETL (which I have pasted below).

In the Unit form, the difference is returned in the specified units from the first argument until the second. For example, DATEDIFF(day, '2011-01-01', '2011-01-08') returns 7. Timestamp and string arguments are converted to dates when the unit requires it (day, month, year, etc.), while date and string arguments are converted to timestamps for other units (second, minute, hour, etc.). This form is largely compatible with other SQL dialects, excluding MySQL.

The formula seems to work when I enter some random dates and validate the tile

DATEDIFF (MONTH, '2020-02-08', '2024-02-09') (It shows 48 when I validate my formula).

However when I run preview for the ETL I am getting an error "Column referenced but not found: MONTH"

Any idea why this is happening and how to fix it?

Thanks in advance.

MarkSnodgrass

Very slick @b_rad . Here is how I might do it.

Essentially, I am using the period_diff to calculate the month difference, but check to see if the day of the start date is greater than the day of the end date and subtract 1 from the resulting period diff since it shouldn't count as a month yet.

Data_Devon

Hello b_rad,

In your random date validation, it looks like you typed in the dates.

I'm assuming that in the working formula, you will have to reference the column that contains the dates you are trying to find the difference between. For example:

DATEDIFF(month,`Start_Date`,`End_Date`) 

If the error you are getting is about a column reference, then I suspect the issue lies in the second two arguments of the formula.

Do you have your start dates listed in one column and referenced in the second argument?

Do you have your end dates listed in one column and referenced in the third argument?

Let us know,

b_rad

Devon,

Thanks for your response but I dont believe what you mentioned is the case. I am using valid columns and actually the formula tile gives an error when I run the ETL or try to "Preview" it even with hardcoded dates. For some reason, its not liking the MONTH parameter in the DATEDIFF function during the Run process.

Data_Devon

Shoot - sorry that wasn't the case. Commenting again for reach and visibility. Standing by for the GOATs 🐐🐐🐐

MarkSnodgrass

Use the PERIOD_DIFF function to calculate the number of months between two dates. You will need to use the DATE_FORMAT function as well to put the dates into the YYYYMM format that the PERIOD_DIFF function requires, but it will return the number of months for you.

PERIOD_DIFF(P1,P2)Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are integer values.

b_rad

Thanks Mark for your response.

However, I dont want to use PERIOD_DIFFas its not accurate.

For example, if I want to find the months between two dates '2025-02-11' and '2025-01-23', the DATEDIFF with the "month" option will give me the accurate answer of 0.

PERIOD_DIFF will give me 1, which is not correct.

Domo seems to have provided the option to do this but somehow its not working. Maybe I'll open a support ticket and find out. Will keep you posted here if I get the solution from Domo support.

MarkSnodgrass

Domo follows MySQL most functions and DATEDIFF does not have the extra parameter that you are referring to.

https://www.w3schools.com/sql/func_mysql_datediff.asp

There is a function called TIMESTAMPDIFF that does support that parameter, but that doesn't seem to be a supported function in Domo. I think you would have to get very creative to try and do date precision. Perhaps by adding an extra case statement to compare the day of the month for each date in addition to the period_diff function.

b_rad

DATEDIFF.png

Domo does seem to have the option in DATEDIFF. Please see the full function description that I see in MagicETL for the DATEDIFF function.

DATEDIFF([unit,]expr1,expr2)Returns the difference between two dates or timestamps. DATEDIFF has two forms: One with only two arguments (omitting the unit) called the "MySQL form", and one with three arguments (with the unit as the first argument) called the "Unit form". The two forms behave completely differently, so it is important to understand the differences and it is inadvisable to mix the forms within a single project.

In the MySQL form, the difference is returned in days from the second argument until the first. For example, DATEDIFF('2011-01-01', '2011-01-08') returns -7. Timestamp and string arguments in this form are always converted to dates. This form is largely compatible with the behavior of MySQL.

In the Unit form, the difference is returned in the specified units from the first argument until the second. For example, DATEDIFF(day, '2011-01-01', '2011-01-08') returns 7. Timestamp and string arguments are converted to dates when the unit requires it (day, month, year, etc.), while date and string arguments are converted to timestamps for other units (second, minute, hour, etc.). This form is largely compatible with other SQL dialects, excluding MySQL.

MarkSnodgrass

Definitely seems like there is a bug going on. When I use the month parameter, it validates and returns a number in the preview.

However, when you run the ETL preview, it errors out and thinks month is a column.

Definitely worth submitting a ticket since they are indicating in their definition that you should be able to use the parameter.

b_rad

I've submitted the ticket. Will update here with how it goes.

b_rad

While I am waiting for Domo to respond. I created my own calculation do this. I did it as multi-step calculation, so that its clear and not confusing. I tried it out with various dates and it seems to produce accurate results.

However, I would really appreciate if some of you try this yourself and confirm that this is accurate.

Please note that if the End Date is less than Start Date then this calculation will output a null and not a negative number. This is by design.

The dataset that feeds this formula tile has a "Start Date" and "End Date" field. You can create your own webform to test this out.

Here are my test dates and the expected result

MarkSnodgrass

Very slick @b_rad . Here is how I might do it.

Essentially, I am using the period_diff to calculate the month difference, but check to see if the day of the start date is greater than the day of the end date and subtract 1 from the resulting period diff since it shouldn't count as a month yet.

b_rad

Thanks @MarkSnodgrass .

As I was reviewing at my calculation, what you have shown did cross my mind.

I agree this is even slicker and better.. :)

I am going to accept that as the answer.