Convert Dates to Calendar Quarter Labels in SQL/Python

Transactions

+----+-----------+
| id | trans_dt  |
+----+-----------+
| 1  | 20230115  |
| 2  | 20230402  |
| 3  | 20230930  |
| 4  | 20231201  |
+----+-----------+

Scenario

Financial reporting needs quarterly sales aggregation from raw numeric dates.

Question

Given a table of transactions where the date is stored as an 8-digit integer (YYYYMMDD), write SQL and/or Python code that converts each date into its calendar quarter label (e.g., 20230215 → 2023_Q 1).

Hints

Convert to date type, extract month, map month to quarter with CASE or arithmetic.

Convert Numeric Dates to Calendar Quarter Labels

Given transaction dates stored as 8-digit integers in YYYYMMDD format, derive each transaction's calendar quarter label.

Use the Transactions table. Return one row per transaction with these columns:

• id
• trans_dt: the original numeric date
• quarter_label: a string in the format YYYY_Qn, such as 2023_Q1

Order the result by id.

Table Transactions

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
trans_dt	INTEGER	NOT NULL

Sample data:

id	trans_dt
1	20230115
2	20230402
3	20230930
4	20231201

Expected output

Each transaction with its original numeric date and derived YYYY_Qn quarter label.

id	quarter_label	trans_dt
1	2023_Q1	20230115
2	2023_Q2	20230402
3	2023_Q3	20230930
4	2023_Q4	20231201

Hints

1. Use integer division to extract the year and month from YYYYMMDD.
2. The quarter is 1 + ((month - 1) / 3).
3. Concatenate the year, _Q, and quarter number, then order by id.