May 24th, 2012, 02:53 PM
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