Calculate Fiscal Year in Snowflake-CodePudding

I'm trying to calculate the fiscal year in Snowflake, and I am running into issues. The fiscal year that I'm trying to calculate for is from July from the current year to June 30th of the following year.

I've tried Googling around, but I cannot seem to get the code to work in Snowflake.

This is my code so far just to get the beginning date for the fiscal year:

CASE WHEN date_part(month,GETDATE()) <= 6
     THEN YEAR(GETDATE())-1
     ELSE try_cast(EXTRACT('year',current_date()) as varchar)   '-07-01'   
     END AS FISCAL_YEAR_TEST

I keep getting this error whenever I try to cast the year extract to a varchar:

Function TRY_CAST cannot be used with arguments of types NUMBER(4,0) and VARCHAR(16777216)

Is there anyway to get the fiscal year date to work in Snowflake?

CodePudding user response：

this shuld do what you want :

select case when quarter(current_date()) > 2 
         then year(current_date())
         else year(current_date()) - 1 
       end as Fiscalyear

CodePudding user response：

Please check below query:

select
    date_trunc(MONTH,add_months(current_date::date, -5))::date as fiscal_start_month,
    last_day(add_months(fiscal_start_month,11))::date fiscal_lastday;

You can also replace hardcoded value 5 with variable which makes dynamic.