Snowflake UDF Functions modeled after Microsoft Excel functions. Written with the help of an AI assistant
This public repository was created to help analysts deploy familiar Excel function capabilities into Snowflake. Currently we have ported the following functions as JavaScript based functions:- WORKDAY function calculates the date after a given number of workdays, while excluding specified holidays
- NETWORKDAYS function calculates the number of workdays between two given dates, while excluding specified holidays
- NETWORKWEEKS function calculates the number of net work weeks between two given dates, while excluding specified holidays
The UDF functions in this repository for WEEKDAY, NETWORKDAYS, and WORKDAY were implemented to use a Snowflake table to acquire the Holidays that are skipped. This article describes instructions for Holidays Table Install Instructions:
- Download from the Samples folder the HOLIDAYS.SQL
- In snowflake select the Table and Schema you want to add the holiday table. This should be the same schema where you are importing the UDF
- Execute the TABLE CREATION and INSERT SQL to place holidays into the table.
The holidays included with this code come from the Federal Reserve bank website:
https://www.frbservices.org/about/holiday-schedules
- 1. UDFs is not globally applied. You need to run a UDF for each schema that you want it to operate.
- 2. When you call a UDF, especially outside of Snowflake, you need the fully qualified name including the table and schema.
This is a Snowflake UDF (User-Defined Function) mimics the Excel WORKDAY function input and results. It adds or subtracts the number of days from a single date and outputs the resulting date while excluding weekends and specified holidays. The function takes three inputs:
START_DATE: a date type input which represents the starting date.
DAYS: a varchar(10) input that represents the number of workdays to be added to the start date.
HOLIDAYS: a variant input that represents the holidays that are to be excluded while calculating the workdays.
HOLIDAYS Table This UDF was designed under an assumption there is a HOLDIAYS table located within the same schema where the UDF is deployed. Please see instructions for Holidays Table Install Instructions
Example SQL to Execute WORKDAY UDF
WITH holidays_array AS (
SELECT ARRAY_TO_STRING(ARRAY_AGG(to_char(HOLIDAY, 'YYYY-MM-DD')), ',') AS HOLIDAYS
FROM DB_NAME.SCHEMA_NAME.HOLIDAYS
)
SELECT workday('2023-01-06', '1',(SELECT HOLIDAYS FROM holidays_array)) as next_workday
FROM DB_NAME.SCHEMA_NAME.TABLE_NAME;
Documentation
The function is written in JavaScript and it first converts the input "HOLIDAYS" into a set of holidays, so that it can be easily checked if a date is a holiday or not. Then it initializes a variable "days_int" with the value of "DAYS" and converts it into an integer.
The function then starts a loop that runs until the "days_int" is greater than 0. In each iteration, it increases the date by 1 and checks if the current date is a weekend(Saturday or Sunday) or a holiday. If it is, it continues to the next iteration, otherwise it decrements the "days_int" by 1.
The function returns the final date after the loop is completed.
Please note that this UDF is based on the standard JavaScript Date object, which means it's based on the local timezone. Therefore, the result may vary depending on the timezone the Snowflake is running.
This is a Snowflake UDF (User-Defined Function) mimics the Excel NETWORKDAYS function input and results. It calculates the number of network days between two given dates, while excluding weekends and specified holidays. The function takes three inputs:
START_DATE: a date type input which represents the starting date.
END_DATE: a date type input which represents the end date.
HOLIDAYS: a varchar(1000000) input that represents the holidays that are to be excluded while calculating the net work days. The holidays are passed in a string format, separated by commas.
HOLIDAYS Table This UDF was designed under an assumption there is a HOLDIAYS table located within the same schema where the UDF is deployed. Please see instructions for Holidays Table Install Instructions
Example SQL to Execute NETWORKDAYS UDF
WITH holidays_array AS (
SELECT ARRAY_TO_STRING(ARRAY_AGG(to_char(HOLIDAY, 'YYYY-MM-DD')), ',') AS HOLIDAYS
FROM DB_NAME.SCHEMA_NAME.HOLIDAYS
)
SELECT NETWORKDAYS('2023-01-10' , '2022-12-20' , (SELECT HOLIDAYS FROM holidays_array)) as network days
FROM DB_NAME.SCHEMA_NAME.TABLE_NAME;
Documentation
The function is written in JavaScript and it first checks if any of the inputs are null, if so it returns null. Then it converts the input "HOLIDAYS" into a set of holidays, so that it can be easily checked if a date is a holiday or not.
The function then checks if the end date is before the start date, if so it swaps the values of start_date and end_date and sets the variable 'isNegative' to true to indicate that the result should be negative.
The function starts a loop that runs from the start date to the end date. In each iteration, it checks if the current date is a weekend(Saturday or Sunday) or a holiday. If it is, it continues to the next iteration, otherwise it increments the "days_int" by 1.
The function returns the final number of net work days. If the start date was greater than the end date, the function returns a negative value.
Please note that this UDF is based on the standard JavaScript Date object, which means it's based on the local timezone. Therefore, the result may vary depending on the timezone the Snowflake is running.
Technically, there is no Excel function for NETWORKWEEKS. This is a Snowflake UDF (User-Defined Function) that calculates the number of net work weeks between two given dates, while excluding specified holidays. The function takes three inputs:
START_DATE: a date type input which represents the starting date.
END_DATE: a date type input which represents the end date.
HOLIDAYS: a varchar(1000000) input that represents the holidays that are to be excluded while calculating the net work weeks. The holidays are passed in a string format, separated by commas.
HOLIDAYS Table This UDF was designed under an assumption there is a HOLDIAYS table located within the same schema where the UDF is deployed. Please see instructions for Holidays Table Install Instructions
Example SQL to Execute NETWORKWEEKS UDF
WITH holidays_array AS (
SELECT ARRAY_TO_STRING(ARRAY_AGG(to_char(HOLIDAY, 'YYYY-MM-DD')), ',') AS HOLIDAYS
FROM DB_NAME.SCHEMA_NAME.HOLIDAYS
)
SELECT NETWORKWEEKS('2023-01-06' , '2023-01-16' , (SELECT HOLIDAYS FROM holidays_array)) as NETWORKWEEKS
FROM DB_NAME.SCHEMA_NAME.TABLE_NAME;
Documentation
The function is written in JavaScript and it first checks if any of the inputs are null, if so it returns null. Then it converts the input "HOLIDAYS" into a set of holidays, so that it can be easily checked if a date is a holiday or not.
The function then sets the current date to the next Monday after the start date and sets the end date to the previous Friday before the end date.
The function starts a loop that runs until the current date is less than the end date. In each iteration, it checks if the current date is a weekend(Saturday or Sunday) or a holiday. If it is not, it increments the "weeks_int" by 1.
The function returns the final number of net work weeks.
Please note that this UDF is based on the standard JavaScript Date object, which means it's based on the local timezone. Therefore, the result may vary depending on the timezone the Snowflake is running.