#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    13
    Rep 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.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    13
    Rep Power
    0
    anybody?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep 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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    73
    Rep 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

IMN logo majestic logo threadwatch logo seochat tools logo