'Calculate Fiscal Year in Snowflake

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 1st of the current year to June 30th of the following year.

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 any way to get the fiscal year date to work in Snowflake?

sqlsnowflake-cloud-data-platform


Solution 1:[1]

This should do what you want :

select case when quarter(current_date()) > 2 
         then date_from_parts(year(current_date()), 07, 01)
         else date_from_parts(year(current_date()) -1, 07, 01)
       end as Fiscalyearbegins
      ,case when quarter(current_date()) > 2 
         then date_from_parts(year(current_date()) +1, 06, 31)
         else date_from_parts(year(current_date()) ,06, 31)
       end as FiscalyearEnds

Solution 2:[2]

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.

Solution 3:[3]

Short answer for end of year March 31

YEAR(DATEADD(YEAR,case when month(date(your_date)) <= 3 THEN -1 ELSE 0 END, your_date)) as fiscal_date

Solution 4:[4]

The logic can be simplified a little by using DATEADD to push the year.

SELECT
    CalendarDate
    , YEAR(DATEADD(MONTH, 6, CalendarDate)) AS FiscalYear
    , DATE_FROM_PARTS(YEAR(DATEADD(MONTH, 6, CalendarDate)) - 1, 07, 01) FiscalYearBegin
    , DATE_FROM_PARTS(YEAR(DATEADD(MONTH, 6, CalendarDate)), 06, 30) AS FiscalYearEnd
FROM
    (
      SELECT CAST('01/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('02/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('03/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('04/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('05/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('06/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('07/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('08/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('09/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('10/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('11/01/2021' AS DATE) AS CalendarDate
      UNION
      SELECT CAST('12/01/2021' AS DATE) AS CalendarDate
    ) AS Dates;

Solution 5:[5]

Here's how I do it:

SELECT 'Fin year start' as Caption,
    (DATEADD (year,
        DATEDIFF (month, '1-Jul-1901', GETDATE()) /12, '1-Jul-1901')) as TheDate

UNION

SELECT 'Fin year end' as Caption,
    (DATEADD (year, 1, DATEADD(d, -1,
    (DATEADD (year, DATEDIFF (month, '1-Jul-1901', GETDATE())/12, '1-Jul-1901'))))) as TheDate

The maths here is simply

  1. Work out the number of months from a fixed financial year starting point, e.g. 1-Jul-1901
  2. Divide by 12 to get the integer number of years; i.e. whole years completed
  3. Then that is our start of financial year.
  4. Next, add a year and subtract a day to get the end of the current financial year.

While there are other solutions above, this one (I feel) lends itself better to assigning to variables and adjusting to give previous or future financial year start/end dates.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chris Albert
Solution 2
Solution 3 Janine Rawnsley
Solution 4 Chris Albert
Solution 5 DavidMWilliams

Related Questions