How to convert T-SQL date functions to Databricks sparkSQL

Question

I have the following T-SQL code

SELECT eomonth(dateadd(quarter, -1,datefromparts(year(sysdatetime()), (month(sysdatetime()) / 4 + 1) * 3, 1)))

And I need to convert it to sparkSQL

I think I can convert the end of the month (eomonth) with the following:

last_day(add_months(current_date())

But I'm not sure how to convert the full T-SQL.

Any thoughts?

Answer 1

Code:1

SELECT last_day(add_months(date_trunc('quarter', current_date()), -1))

The above code first truncates the current date to the beginning of the current quarter using the date_trunc function. It then subtracts one month from the resulting date using the add_months function, and finally gets the last day of the resulting month using the last_day function.

Code:2

select  date_add( date_trunc('quarter', current_date()),-1)

The above query subtracts one day from the beginning of the current quarter.

Any of the above codes will give the result 2023-03-31.