Snowflake UDF to extract FY from a date.

extract_fy.sql

CREATE OR REPLACE FUNCTION extract_fy(date_to_convert varchar, date_format varchar DEFAULT 'YYYY-MM-DD')
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
SELECT 
CASE
	WHEN MONTH(TRY_TO_DATE(date_to_convert, date_format)) >= 7 THEN 
		CONCAT('FY', RIGHT(YEAR(TRY_TO_DATE(date_to_convert, date_format)) + 1, 2))
	ELSE CONCAT('FY', RIGHT(YEAR(TRY_TO_DATE(date_to_convert, date_format)), 2))
END AS financial_year
$$
;

Note that this is for Australia.

The Australian financial year starts on 1 July, with the last day occurring the following year on 30 June.

Examples:

SELECT EXTRACT_FY('2024-07-01')
FY25

SELECT EXTRACT_FY('07-01-2024', 'MM-DD-YYYY')
FY25

SELECT EXTRACT_FY('2024-06-30')
FY24

SELECT EXTRACT_FY('2024')
[NULL]

SELECT EXTRACT_FY('2024', 'YYYY')
FY24