Dates with different financial years and quarters

Calendar quarters start in January. Financial years often start in another month, so the quarter number and reporting year must be shifted. These examples retain the original April–March and October–September cases.

April to March financial year

SELECT DATE_FORMAT(payment_dt, '%Y-%M-%d') AS report_date,
CASE WHEN QUARTER(payment_dt) = 1
     THEN CONCAT(YEAR(payment_dt) - 1, '-Q', QUARTER(payment_dt) + 3)
     ELSE CONCAT(YEAR(payment_dt), '-Q', QUARTER(payment_dt) - 1)
END AS financial_quarter
FROM plus2_bills;

October to September financial year

SELECT DATE_FORMAT(payment_dt, '%Y-%M-%d') AS report_date,
CASE WHEN QUARTER(payment_dt) != 4
     THEN CONCAT(YEAR(payment_dt) - 1, '-Q', QUARTER(payment_dt) + 1)
     ELSE CONCAT(YEAR(payment_dt), '-Q', QUARTER(payment_dt) - 3)
END AS financial_quarter
FROM plus2_bills;

Display a two-year financial-year label

The same quarter logic can display labels such as 2015-16 - Q1.

April to March

SELECT DATE_FORMAT(payment_dt, '%Y-%M-%d') AS report_date,
CASE WHEN QUARTER(payment_dt) = 1
     THEN CONCAT(YEAR(payment_dt) - 1, '-', DATE_FORMAT(payment_dt, '%y'), ' - Q', QUARTER(payment_dt) + 3)
     ELSE CONCAT(YEAR(payment_dt), '-', LPAD((YEAR(payment_dt) + 1) MOD 100, 2, '0'), ' - Q', QUARTER(payment_dt) - 1)
END AS financial_quarter
FROM plus2_bills;

October to September

SELECT DATE_FORMAT(payment_dt, '%Y-%M-%d') AS report_date,
CASE WHEN QUARTER(payment_dt) != 4
     THEN CONCAT(YEAR(payment_dt) - 1, '-', DATE_FORMAT(payment_dt, '%y'), ' - Q', QUARTER(payment_dt) + 1)
     ELSE CONCAT(YEAR(payment_dt), '-', LPAD((YEAR(payment_dt) + 1) MOD 100, 2, '0'), ' - Q', QUARTER(payment_dt) - 3)
END AS financial_quarter
FROM plus2_bills;

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: