Spark SQL 2.4实现SQL Server上季度末日期计算方案问询

Got it, let's solve this problem of getting the last day of the previous quarter for a given date in Spark SQL 2.4, matching the behavior of your original SQL Server query.

The Spark SQL Solution #

Here's the equivalent query that produces exactly the output you need:

SELECT
  input_date,
  date_sub(
    trunc(cast(input_date AS DATE), 'quarter'),
    1
  ) AS end_date
FROM your_table_name;

How It Works (Breakdown vs. SQL Server Logic) #

Let's map this to your original SQL Server query to show the equivalent steps:

1. cast(input_date AS DATE): Same as your original cast('2020-09-09' AS DATE) – ensures we're working with a proper date type.
2. trunc(..., 'quarter'): This replaces the dateadd(qq, datediff(qq, 0, [date]), 0) part of your SQL Server query. The trunc function with 'quarter' parameter returns the first day of the input date's current quarter (e.g., 2020-09-09 becomes 2020-07-01).
3. date_sub(..., 1): Replaces dateadd(dd, -1, ...) – subtracts 1 day from the quarter's first day to get the last day of the previous quarter.

Verification with Your Sample Data #

Let's test this against your input/output pairs to confirm it works:

• Input: 2020-01-21 → trunc gives 2020-01-01 → date_sub gives 2019-12-31 ✔️
• Input: 2020-02-06 → trunc gives 2020-01-01 → date_sub gives 2019-12-31 ✔️
• Input: 2020-04-15 → trunc gives 2020-04-01 → date_sub gives 2020-03-31 ✔️
• Input: 2020-07-10 → trunc gives 2020-07-01 → date_sub gives 2020-06-30 ✔️
• Input: 2020-10-20 → trunc gives 2020-10-01 → date_sub gives 2020-09-30 ✔️
• Input: 2021-02-04 → trunc gives 2021-01-01 → date_sub gives 2020-12-31 ✔️

Notes #

• If your input_date column is already a DATE type (not a string), you can omit the cast(input_date AS DATE) part and just use trunc(input_date, 'quarter').
• This works reliably in Spark SQL 2.4 – the trunc function's 'quarter' parameter is fully supported in this version.

内容的提问来源于stack exchange，提问作者dexter80