generate_date_calendar_with_holidays - BigQuery

Function

generate_date_calendar_with_holidays(table_name, start_date, end_date, country)

Description

Generate Date Calendar With Holidays - Generates a complete date calendar table named <table_name> with holidays for the specified <country>, from <start_date> to <end_date>. Holidays are available between 2000-2100.

Example Query

CALL `justfunctions.eu.generate_date_calendar_with_holidays`("justfunctions.test.date_calendar","2020-01-01","2030-01-01","GR")
                                            
/*--Output--
- date_id: 20200101 - date: 2020-01-01 - epoch: 1577836800 - day_suffix: 1th - day_name: Wednesday - day_name_abbr: Wed - day_of_week: 4 - day_of_month: 1 - day_of_quarter: 1 - day_of_year: 1 - week_of_month: 1 - week_of_year: 1 - week_of_year_iso: 2020-W01-3 - month: 1 - month_name: January - month_name_abbr: Jan - quarter: 1 - quarter_name: Q1 - year: 2020 - start_of_week: 2019-12-30 - start_of_week_monday: 2019-12-29 - start_of_week_saturday: 2019-12-28 - start_of_month: 2020-01-01 - start_of_midmonth: 2020-01-15 - start_of_quarter: 2020-01-01 - start_of_year: 2020-01-01 - end_of_week: 2020-01-03 - end_of_month: 2020-01-31 - end_of_quarter: 2020-03-31 - end_of_year: 2020-12-31 - start_of_fiscal_year: 2019-10-01 - end_of_fiscal_year: 2020-09-30 - yyyymm: 202001 - yyyymmdd: 20200101 - month_desc: 2020-Jan - quarter_desc: 2020-Q1 - week_desc: 2020-W1 - is_weekend: FALSE - season: Winter - start_of_season: 2019-12-01 - holiday_name: Πρωτοχρονιά - holiday_country: gr - special_day_name : New Year's Day - moon_illumination : 29.95 - days_in_a_month: 31 - is_leap_year: 1
*/

Statement

CREATE OR REPLACE PROCEDURE `your_project_id.your_dataset_id.generate_date_calendar_with_holidays`(`table_name` string, `start_date` string, `end_date` string, `country` string)
options(
    description = '''Generates a complete date calendar table named <table_name> with holidays for the specified <country>, from <start_date> to <end_date>. Holidays are available between 2000-2100.'''
)
BEGIN

-- Drop the table if it exists
EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || table_name;

-- Create the date_calendar table
EXECUTE IMMEDIATE 'CREATE TABLE ' || table_name || ' (date_id INT64 NOT NULL, date DATE NOT NULL, epoch INT64 NOT NULL, day_suffix STRING NOT NULL, day_name STRING NOT NULL, day_name_abbr STRING NOT NULL, day_of_week INT64 NOT NULL, day_of_month INT64 NOT NULL, day_of_quarter INT64 NOT NULL, day_of_year INT64 NOT NULL, week_of_month INT64 NOT NULL, week_of_year INT64 NOT NULL, week_of_year_iso STRING NOT NULL, month_ INT64 NOT NULL, month_name STRING NOT NULL, month_name_abbr STRING NOT NULL, quarter INT64 NOT NULL, quarter_name STRING NOT NULL, year INT64 NOT NULL, start_of_week DATE NOT NULL, start_of_week_monday DATE NOT NULL,start_of_week_saturday DATE NOT NULL, start_of_month DATE NOT NULL, start_of_midmonth DATE NOT NULL, start_of_quarter DATE NOT NULL, start_of_year DATE NOT NULL, end_of_week DATE NOT NULL, end_of_month DATE NOT NULL, end_of_quarter DATE NOT NULL, end_of_year DATE NOT NULL,start_of_fiscal_year DATE NOT NULL,end_of_fiscal_year DATE NOT NULL, yyyymm STRING NOT NULL, yyyymmdd STRING NOT NULL, month_desc STRING, quarter_desc STRING, week_desc STRING, is_weekend BOOL NOT NULL, season STRING, start_of_season DATE, holiday_name STRING, holiday_country STRING, special_day_name STRING, moon_illumination FLOAT64,days_in_month INT64, is_leap_year INT64);';

-- Populate the table
EXECUTE IMMEDIATE 'INSERT INTO ' || table_name || ' WITH date_range AS (SELECT date FROM UNNEST(GENERATE_DATE_ARRAY("' || start_date || '","' || end_date  || '")) AS date) SELECT CAST(FORMAT_DATE("%Y%m%d", date) AS INT64) AS date_id, date AS date, UNIX_SECONDS(TIMESTAMP(date)) AS epoch, CONCAT(CAST(EXTRACT(DAY FROM date) AS STRING), "th") AS day_suffix, FORMAT_DATE("%A", date) AS day_name, FORMAT_DATE("%a", date) AS day_name_abbr, EXTRACT(DAYOFWEEK FROM date) AS day_of_week, EXTRACT(DAY FROM date) AS day_of_month, EXTRACT(DAY FROM date) - EXTRACT(DAY FROM DATE_TRUNC(date, QUARTER)) + 1 AS day_of_quarter, EXTRACT(DAYOFYEAR FROM date) AS day_of_year, EXTRACT(WEEK FROM date) - EXTRACT(WEEK FROM DATE_TRUNC(date, MONTH)) + 1 AS week_of_month, CAST(FORMAT_DATE("%V", date) AS INT64) AS week_of_year, FORMAT_DATE("%G-W%V-%u", date) AS week_of_year_iso, EXTRACT(MONTH FROM date) AS month, FORMAT_DATE("%B", date) AS month_name, FORMAT_DATE("%b", date) AS month_name_abbr, EXTRACT(QUARTER FROM date) AS quarter, CONCAT("Q", CAST(EXTRACT(QUARTER FROM date) AS STRING)) AS quarter_name, EXTRACT(YEAR FROM date) AS year, DATE_TRUNC(date, WEEK(MONDAY)) AS start_of_week, DATE_TRUNC(date, WEEK(MONDAY)) AS start_of_week_monday ,DATE_TRUNC(date, WEEK(SATURDAY)) AS start_of_week_saturday, DATE_TRUNC(date, MONTH) AS start_of_month, DATE_ADD(DATE_TRUNC(date, MONTH), INTERVAL 14 DAY) AS start_of_midmonth, DATE_TRUNC(date, QUARTER) AS start_of_quarter, DATE_TRUNC(date, YEAR) AS start_of_year, DATE_ADD(DATE_TRUNC(date, WEEK(SATURDAY)), INTERVAL 6 DAY) AS end_of_week, LAST_DAY(date, MONTH) AS end_of_month, LAST_DAY(date, QUARTER) AS end_of_quarter, LAST_DAY(date, YEAR) AS end_of_year,     CAST(IF(EXTRACT(MONTH FROM date) >= 10,     DATE(EXTRACT(YEAR FROM date), 10, 1),    DATE(EXTRACT(YEAR FROM date) - 1, 10, 1)) AS DATE) AS start_of_fiscal_year,CAST(IF(EXTRACT(MONTH FROM date) >= 10,     DATE(EXTRACT(YEAR FROM date) + 1, 9, 30),     DATE(EXTRACT(YEAR FROM date), 9, 30) ) AS DATE) AS end_of_fiscal_year,FORMAT_DATE("%Y%m", date) AS yyyymm, FORMAT_DATE("%Y%m%d", date) AS yyyymmdd, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-", FORMAT_DATE("%b", date)) AS month_desc, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-Q", CAST(EXTRACT(QUARTER FROM date) AS STRING)) AS quarter_desc, CONCAT(CAST(EXTRACT(YEAR FROM date) AS STRING), "-","W",CAST(FORMAT_DATE("%V", date) AS INT64)) AS week_desc, CASE WHEN EXTRACT(DAYOFWEEK FROM date) IN (6, 7) THEN TRUE ELSE FALSE END AS is_weekend, your_project_id.your_dataset_id.find_season(date).season season, your_project_id.your_dataset_id.find_season(date).start_of_season start_of_season , holiday_name, holiday_country, special_day_name,your_project_id.your_dataset_id.find_moon_illumination(date) moon_illumination,COUNT(date) OVER (PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY date) AS days_in_month,IF(COUNT(date) OVER (PARTITION BY EXTRACT(YEAR FROM date) ORDER BY date) = 365, 0, 1) AS is_leap_year FROM date_range d LEFT JOIN (SELECT * FROM your_project_id.your_dataset_id.date_holidays WHERE holiday_country=UPPER("' || country || '")) dh ON dh.holiday_date=d.date LEFT JOIN your_project_id.your_dataset_id.date_special_days sd ON sd.special_day_date=d.date;';


END;

Regions

justfunctions.eu.generate_date_calendar_with_holidays(table_name, start_date, end_date, country),
justfunctions.us.generate_date_calendar_with_holidays(table_name, start_date, end_date, country)

Type

User Defined SQL Procedure

How to Use

Frequently Asked Questions

User-Defined Functions (UDFs) in Google BigQuery are custom functions that you can create to perform operations that aren't available through the standard SQL functions. These UDFs allow you to extend BigQuery's SQL capabilities to suit your specific data processing needs. JustFunctions is a collection of open-source user-defined functions (UDFs).

JustFunctions is a collection of open-source User-Defined Functions (UDFs) designed to extend the capabilities of Google BigQuery. These functions cover a wide range of applications, including text manipulation, URL processing, date processing, email handling, similarity measures, and more. Moreover, JustFunctions is frequently updated to include more use cases.

We welcome any feedback or questions you may have. You can Contact us or report an issue on Github.

Functions and procedures from JustFunctions can be used directly in any of your projects.
To start, simply click 👆 on any function,  Copy  the 'Example Query' and run it in your BigQuery console.
You can also  Copy  the 'Statement' to create your own private user-defined function.

Yes, JustFunctions is completely free to use.

Yes, currently JustFunctions is only available for Google BigQuery. In the future, we will also support PostgreSQL.

See something wrong? Contact us or report an issue on Github.