Select Financial year

Dev Shed Forums Sponsor:

April 26th, 2012, 09:46 PM

slamdunkrookie

Registered User

Join Date: Dec 2008

Posts: 13

Time spent in forums: 1 h 23 m 50 sec
Reputation Power: 0

Select Financial year

How to write a select query for financial year? The financial is from 1st July to 30th June each year. Here's the pseudo code (dd-mm-yyyy):

If extract(month from current_date) < 7 then

select date between ('01-07-date_part('year', current_date)'-1) and ('30-06-date_part('year', current_date')

else

select date between ('01-07-date_part('year', current_date)') and ('30-06-date_part('year', current_date')

End if

Appreciate any help.

May 1st, 2012, 02:28 AM

slamdunkrookie

Registered User

Join Date: Dec 2008

Posts: 13

Time spent in forums: 1 h 23 m 50 sec
Reputation Power: 0

anybody?

May 22nd, 2012, 04:41 PM

joeflinthiggins

Registered User

Join Date: May 2012

Posts: 14

Time spent in forums: 4 h 20 m 33 sec
Reputation Power: 0

Hello,

If you are trying to simply return the financial year (July 1st through June 30th) from a date column, then this should give you what you need:

select
case
when to_char(now(), 'mm')::int >= 7
then to_char(now(), 'yyyy')::int +1
else to_char(now(), 'yyyy')::int
end

You can replace now() with your date column.

Let me know if I've misunderstood your end goal.

Regards,

Joe

May 24th, 2012, 02:53 PM

FlyingBeetroot

Contributing User

Join Date: May 2012

Posts: 73

Time spent in forums: 20 h 24 m 52 sec
Reputation Power: 35

Call me foolish, but I hate hard coding this type of thing into queries.

Consider this approach:

Code:

CREATE TABLE financial_calendar (
  financial_year character varying,
  year_effective_from date,
  year_effective_to date)
-- put a primary key on financial_year

populate this table backwards 10 years, and forwards 100 (some ridiculous number it will never reach).

Table date should look something like this:
SELECT * FROM financial_calendar;

--
financial_year | year_effective_from | year_effective_to
2007                 01/07/2006                30/06/2007
2008                 01/07/2007                30/06/2008
.....

Then your query becomes

SELECT
  financial_year
FROM
  financial_calendar
WHERE
  now() between year_effective_from AND year_effective_to

Pros:
- If you ever want this to extend to other countries; you just re-populate the table with the relevant fin year data
- Fast query; no internal functions (casting), and no chance of screwing up the query.

Cons:
- Another table (which is really here nor there)
- You might populate it incorrectly

I do this where I know the data is static; but not consistent in all use cases (in this example: fin year July->June in Australia, but America it's Oct->sept

Select Financial year

Developer Shed Advertisers and Affiliates