Date difference in Months

[AMadan]

Hi Team,

I am working on migration from Sql server to databricks environment.

I encounter a challenge where Databricks and sql server giving different results for date difference function. Can you please help?

--SQL SERVER
SELECT DATEDIFF(MONTH , '2007-01-01' , '2007-02-28')   ;  --1
SELECT DATEDIFF(MONTH , '2007-01-30' , '2007-02-28')   ;  --1
SELECT DATEDIFF(MONTH , '2007-01-31' , '2007-02-28')   ;  --1

--Databricks
SELECT DATEDIFF(MONTH , '2007-01-01' , '2007-02-28')   ;  --1
SELECT DATEDIFF(MONTH , '2007-01-30' , '2007-02-28')   ;  --0
SELECT DATEDIFF(MONTH , '2007-01-31' , '2007-02-28')   ;  --0

select floor(months_between('2007-02-28', '2007-01-01'))  --1
select floor(months_between('2007-02-28', '2007-01-30'))  --0
select floor(months_between('2007-02-28', '2007-01-31'))  --1

[-werners-]

While I was pretty sure it has to do with T-SQL not following ANSI standards, I could not actually tell you what exactly the difference is.  So I asked chatgpt and here we go:

The difference between DATEDIFF(month, date1, date2) in T-SQL and ANSI SQL is that T-SQL uses a different algorithm to calculate the number of months between two dates than ANSI SQL. T-SQL counts the number of month boundaries crossed between the two dates, regardless of the day part of the dates. For example, DATEDIFF(month, ‘2023-01-31’, ‘2023-02-01’) returns 1 in T-SQL, because there is one mo...1. However, ANSI SQL uses a more precise calculation that takes into account the day part of the dates. For example, DATEDIFF(month, ‘2023-01-31’, ‘2023-02-01’) returns 0.032258064516129 in ANSI SQL, beca...2. Therefore, T-SQL may return a larger or smaller value than ANSI SQL for the same pair of dates, depending on how many month boundaries are crossed.

you could try to use months_between() function instead of datediff (and perhaps also a round function).