Figuring out the number of years or months between 2 dates shouldn’t be hard. For some reason though, for a lot of database systems it is.
Take, for example, SQL Server. If I want to know the number of months between 1/1/2023 and 7/4/2023, it’s not too hard.
A simple DATEDIFF function and we’re golden.
Now, let’s try something which shouldn’t be any more complicated. Let’s find the number of months between 1/7/2023 and 7/4/2023. Since the 7th is after the 4th, it should be only 5, right? But what does SQL Server return?
Uhm… No. Is it close enough for government work? Maybe…
So, let’s try it in Snowflake! It has to work there, right?
Argh… Something here is not shivering me timbers…
Thankfully, there is an easy workaround in SnowFlake for just this issue.
Yes, my friends, there is a Snowflake for just this eventuality. That function is MONTHS_BETWEEN.
First, let’s notice that the answer is of a FLOAT data type. If I only want the integer portion, please use the FLOOR function to get the correct number of whole months.
I bring this up because if you convert the data to an INT or ROUND the data, you’ll get the same wrong answer you had before from DATEDIFF.
Second, notice that you must ensure that the data used within the function are of a date type. For example, if I used the data without it being converted to a date, but I see that it truly is, I get an error.
Just use a field that is always a date data type or be sure to convert it to a date type in your queries.
Next, notice that the dates are actually reversed in the MONTHS_BETWEEN function and the DATEDIFF function. Just something to keep in mind as you’re working with these functions. Unless you just like working with negative numbers…
But, finally, dear reader, there is an issue with the MONTHS_BETWEEN SnowFlake function you should be aware of.
Notice that somehow the number of months between 3/28 and 2/28 vs 3/31 and 2/28 are exactly the same down to the furthest decimal point while the number of months between 3/30 and 2/28 is more than either of them!
This is due to the fact that 2/28 is the end of the month (at least for the year in question) and so is 3/30. But 3/28 is a valid date as well and it truly is exactly one month from 2/28.
Is this truly wrong in Snowflake? Yes and no… End-of-month calculations are a thing. But this just looks wrong! And so it is. Sometimes…
Is there a way to get it right in both databases? Abso-freakin-lutely.
With a DATEDIFF function and a CASE statement, you’re home-free!
And with that my friends, hopefully, you’ve learned yet another function in Snowflake and how to use it. But, as always, be careful of the data you use with it and make sure the answer checks out. Until next time my friends.