Convert integer dates to quarters

You are given a table `date_inputs` of raw date keys stored as 8-digit strings in `YYYYMMDD` format, together with an optional fiscal-year start month. Write a single **PostgreSQL** query that converts each date key into a quarter label. ### Table: `date_inputs` | column | type | notes | |---|---|---| | `id` | INT (PK) | row identifier | | `date_key` | VARCHAR(20) | an 8-digit date in `YYYYMMDD` format; may contain leading/trailing whitespace | | `fiscal_start_month` | INT | optional; `NULL` or `1` = standard calendar quarters; a value `2`–`12` is the first month of the fiscal year | Calendar quarters follow the Gregorian calendar: **Q1 = Jan–Mar, Q2 = Apr–Jun, Q3 = Jul–Sep, Q4 = Oct–Dec**. ### Requirements Return one row for **every** row in `date_inputs`, with columns **`id`, `date_key`, `fiscal_start_month`, `quarter_label`** (in that order), sorted by `id` ascending. The `quarter_label` must be a string of the form `'YYYY-Qn'`, computed as follows: 1. **Trim** any leading/trailing whitespace from `date_key`, then parse it strictly as a real calendar date in `YYYYMMDD` format. The `quarter_label` must be **`NULL`** for any value that is not a valid date, including: - month `00` or `13`+ (e.g. `20251301`), - day `00`, - a day that is too large for the month (e.g. `20250230` — Feb never has 30 days), - Feb 29 in a non-leap year (e.g. `20210229`), - any value that is not exactly 8 digits after trimming. 2. When `fiscal_start_month` is `NULL` or `1`, use the standard **calendar** year and calendar quarter. 3. When `fiscal_start_month` is between `2` and `12`, use the **fiscal** year and fiscal quarter. The fiscal year starts in month `fiscal_start_month` and the four fiscal quarters are the four 3-month blocks counting from that month. For example, with `fiscal_start_month = 4` (April start, so the fiscal year runs Apr–Mar): - `2025-03-31` → `'2024-Q4'` (March is the last month of the fiscal year that began Apr 2024), - `2025-04-01` → `'2025-Q1'` (April begins the fiscal year labeled 2025). > Note: the original prompt assumed a SQL Server environment (`TRY_CONVERT`, `CROSS APPLY`, `FORMAT`). This version targets **PostgreSQL**, so use Postgres-native safe parsing and date functions instead.

You are given a table `date_inputs` that stores dates as 8-character strings in `YYYYMMDD` format, along with a per-row `fiscal_start_month` (the calendar month, 1-12, on which that row's fiscal year begins). Some `date_key` values contain surrounding whitespace, and some are invalid (for example, an impossible month such as `13`). Write a single PostgreSQL query that converts each `date_key` into a **quarter label** and returns one row per input. Quarter-label rules: 1. **Parse and validate.** Trim surrounding whitespace, then treat the value as a valid date only if it is exactly 8 digits, its month is between 1 and 12, its day is between 1 and 31, AND it round-trips (re-formatting the parsed date back to `YYYYMMDD` equals the trimmed input). Any value that fails validation (e.g. month `13`) produces a `NULL` quarter label. 2. **Calendar quarters** (when `fiscal_start_month` is `NULL` or `1`): the label is `'<calendar year>-Q<n>'`, where `n = floor((month - 1) / 3) + 1`. For example `2024-02-29` -> `'2024-Q1'`, `2025-12-31` -> `'2025-Q4'`. 3. **Fiscal quarters** (when `fiscal_start_month` is 2-12): shift the month by the fiscal start. The fiscal quarter is `n = floor(((month - fiscal_start_month + 12) % 12) / 3) + 1`. The fiscal year is the calendar year if `month >= fiscal_start_month`, otherwise the calendar year minus 1. For example, with `fiscal_start_month = 4`, `2025-03-31` falls in fiscal year 2024, quarter 4 -> `'2024-Q4'`, while `2025-04-01` is the first day of fiscal year 2025 -> `'2025-Q1'`. Using the provided sample rows with `id` 1 through 6, return the columns **`id`, `date_key`, `quarter_label`, `fiscal_start_month`** (in that order), one row per input, **sorted by `id` ascending**. Your output must demonstrate correct handling of the leap day (id 1), end-of-year and start-of-year calendar dates (ids 3 and 4), a fiscal-year offset (id 5), and an invalid date (id 6, which yields `NULL`).