Question 250344 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How To Get Financial Year Month(April to March) based on Year in Postgres

I need to get Financial Year Month(April to March) based on Year in Postgres Query…

Like if passing 2022 then I need to get(12 month list) April-2022 to March-2023 month list.

I have tried using EXTRACT() Function date to year but not getting exact result.

Tags:

3

Answers


  1. 0 
    SELECT to_char(t, 'Month-yyyy') AS financial_month
from generate_series(
             date 'yyyy-04-01'::date, -- Replace yyyy with your desired year
             date 'yyyy-04-01'::date + interval '1 year - 1 day',
             interval '1 month'
         ) t

    This query generates a series of dates starting from April 1st of the specified year and ending on March 31st of the following year (a financial year). It then uses the to_char function to format these dates as "Month-YYYY" (e.g., "April-2022", "May-2022", …, "March-2023").

    Login or Signup to reply.
  2. 0

    You can use this generally to generate the list for the financial year by changing the year:

    SELECT
    to_char(start_date, 'Month-YYYY') AS financial_month
FROM (
    SELECT 
        generate_series(
            date 'year-04-01',
            date 'year-03-01',
            interval '1 month'
        ) AS start_date
) AS months;

    I have replaced year to 2022 and 2023 to get the list you wanted in your example:

    SELECT
    to_char(start_date, 'Month-YYYY') AS financial_month
FROM (
    SELECT 
        generate_series(
            date '2022-04-01',
            date '2023-03-01',
            interval '1 month'
        ) AS start_date
) AS months;

    You will the following output:

     financial_month 
-----------------
 April-2022
 May-2022
 June-2022
 July-2022
 August-2022
 September-2022
 October-2022
 November-2022
 December-2022
 January-2023
 February-2023
 March-2023
(12 rows)
    Login or Signup to reply.
  3. 0

    You can as well try this query to get Financial Year Month based on Year

    SELECT TO_CHAR(generate_series, 'Month-YYYY')
FROM generate_series(
    DATE '2022-04-01',
    DATE '2022-04-01' + INTERVAL '1 year - 1 day',
    INTERVAL '1 month'
) AS generate_series;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search