Calculate Fiscal Year in Snowflake

Jenny

3 years ago

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?