April 26th, 2012, 09:46 PM
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')
select date between ('01-07-date_part('year', current_date)') and ('30-06-date_part('year', current_date')
Appreciate any help.
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:
when to_char(now(), 'mm')::int >= 7
then to_char(now(), 'yyyy')::int +1
else to_char(now(), 'yyyy')::int
You can replace now() with your date column.
Let me know if I've misunderstood your end goal.
Call me foolish, but I hate hard coding this type of thing into queries.
Consider this approach:
CREATE TABLE financial_calendar (
financial_year character varying,
-- 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
now() between year_effective_from AND year_effective_to
- 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.
- 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