Search code examples
sqliteconcatenationdate-arithmeticstrftime

How can I calculate Financial year from created date using SQLite

Suppose I say my Financial year start date is 2000-02-01. Then Financial year should be

2019-03-01 to 2020-02-29
2020-03-01 to 2021-02-28
2021-03-01 to 2022-02-28
.
.
.

I want to show a column of the financial year like below using SQLite.

Product created Date Financial Year
Apple 2019-05-28 FY 2019-2020
Apple 2020-01-15 FY 2019-2020
Banana 2020-04-22 FY 2020-2021
Mango 2021-10-15 FY 2021-2022
Solution

  • There is no need for a separate table.

    All you need is the function strftime():

    SELECT *,
       'FY ' || strftime('%Y', createdDate, '-2 month') || '-' ||
       strftime('%Y', createdDate, '-2 month', '+1 year') FinancialYear
FROM products;

    See the demo.