Date difference in Months

AMadan — Thu, 21 Sep 2023 12:02:58 GMT

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

Re: Date difference in Months

-werners- — Thu, 21 Sep 2023 12:37:36 GMT

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 month boundary crossed between January and February 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, because there are only 1 day out of 31 days in January between the two dates 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).

topic Re: Date difference in Months in Data Engineering

Date difference in Months

Re: Date difference in Months