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.

CodePudding user response：

I suspect maintaining a separate table which contains the custom financial year ranges along with the text label you want to display for each:

financial_years:

start_date | end_date   | label
2019-03-01 | 2020-02-29 | FY 2019-2020
2020-03-01 | 2021-02-28 | FY 2020-2021
2021-03-01 | 2022-02-28 | FY 2021-2022

Now all we need to do is join your table of products to the above table:

SELECT p.Product, p.created_date, fy.label
FROM products p
INNER JOIN financial_years fy
    ON p.created_date BETWEEN fy.start_date AND fy.end_date;

CodePudding user response：

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.

CodePudding user response：

A solution with createdDate REAL and CASE WHEN:

select Product,
  date(createdDate) as createdDate, 
  case when (abs(strftime('%m', createdDate)) < 3)
       then 'FY ' || cast(strftime('%Y', createdDate) - 1 as text) || '-' || strftime('%Y', createdDate)
       else 'FY ' || strftime('%Y', createdDate) || '-' || cast(strftime('%Y', createdDate)   1 as text)
  end AS FinancialYear
from `products`;

See the demo here