Convert integer dates to quarters

Given an 8-digit integer date_key in YYYYMMDD (e.g., 20240331), write: (a) a SQL expression and (b) a Python function that convert it to a quarter label 'YYYY-Qn' using the Gregorian calendar (Q1=Jan–Mar, Q2=Apr–Jun, Q3=Jul–Sep, Q4=Oct–Dec). Requirements:

• Validate the date (reject or return NULL for impossible values such as 20250230, month=00/13, day=00, or 20210229 in a non-leap year).
• Support an optional fiscal_start_month s (1–12). If s ≠ 1, compute fiscal year and fiscal quarter; for example, with s=4 (Apr), 20250331 -> '2024-Q4' and 20250401 -> '2025-Q1'.
• Treat inputs that are strings with leading/trailing whitespace and leading zeros appropriately.
• Show the exact outputs for: 20240229, 20230228, 20251231, 20250101, 20250331 with s=4, and an invalid 20251301.

Convert integer-style dates to calendar or fiscal quarters in SQL

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.

Table date_inputs

column	type	constraints
id	INT	PRIMARY KEY
date_key	VARCHAR(20)	NOT NULL
fiscal_start_month	INT

Sample data:

date_key	fiscal_start_month	id
20240229	1	1
20230228	1	2
20251231	1	3
20250101	1	4
20250331	4	5
20251301	1	6
20250401	4	7
20210229	1	8

Expected output

One row per input (sorted by id) with the computed calendar or fiscal quarter label 'YYYY-Qn'; invalid date_key values yield a NULL quarter_label.

date_key	fiscal_start_month	id	quarter_label
20240229	1	1	2024-Q1
20230228	1	2	2023-Q1
20251231	1	3	2025-Q4
20250101	1	4	2025-Q1
20250331	4	5	2024-Q4
20251301	1	6	NULL
20250401	4	7	2025-Q1
20210229	1	8	NULL

Hints

1. Postgres has no TRY_CONVERT; validate the 8 digits with a regex (~ '^[0-9]{8}$'), range-check the month and day yourself, then build the date with make_date(...) only when valid.
2. Don't forget the per-month day maxima and the leap-year rule (year % 4 = 0 AND (year % 100 <> 0 OR year % 400 = 0)) so Feb 29 is only accepted in leap years.
3. For the fiscal quarter, shift the month into a 0–11 offset from the start month with (month - fiscal_start_month + 12) % 12, then integer-divide by 3 and add 1; the fiscal year is the prior calendar year when the month is before the start month.

Verify quarter labels for specific date examples

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).

Table date_inputs

column	type	constraints
id	INT	PRIMARY KEY
date_key	VARCHAR(20)	NOT NULL
fiscal_start_month	INT

Sample data:

date_key	fiscal_start_month	id
20240229	1	1
20230228	1	2
20251231	1	3
20250101	1	4
20250331	4	5
20251301	1	6
20250401	4	7

Expected output

One row per input id 1-6: id, original date_key, the computed quarter_label (NULL for the invalid month-13 date), and fiscal_start_month, ordered by id.

date_key	fiscal_start_month	id	quarter_label
20240229	1	1	2024-Q1
20230228	1	2	2023-Q1
20251231	1	3	2025-Q4
20250101	1	4	2025-Q1
20250331	4	5	2024-Q4
20251301	1	6	NULL

Hints

1. PostgreSQL's to_date is lenient (it rolls month 13 over to the next year), so validate the components yourself and/or re-format the parsed date with TO_CHAR and compare it back to the input.
2. Trim whitespace with BTRIM before parsing, and use a regex like '^[0-9]{8}$' to require exactly 8 digits.
3. For fiscal quarters, shift the month by (month - fiscal_start_month + 12) % 12 before dividing by 3, and subtract 1 from the year when the month is earlier than the fiscal start.