Alternative for Sys_Calendar of teradata in snowflake

Question

Do we have any alternative for teradata Sys_Calendar.CALENDAR function in snowflake? I couldnt find any table or builtin functions to achive this

[The Sys_Calendar.CALENDAR system view helps to extend the properties of a DATE data type column by means of a join. The columns of the view contain data only for the active calendar for the session. The calendar dates range from 1900 to 2100 and are stored in a table in the Sys_Calendar database.]

Answer 1

BASIC -> BELOW

ADVANCED -> AUTOMATICALLY PULLS IN HOLIDAYS AND PRETTY EMOJI FLAGS FOR YOUR COUNTRY BASED ON CURRENT_IP()

WITH GAPLESS_ROW_NUMBERS  AS (
SELECT ROW_NUMBER() OVER (ORDER BY seq4()) - 1 as "ROW_NUMBER" 
FROM TABLE(GENERATOR(rowcount => 366 * (2100 - 1970)) ) 
-- rowcount is 366 days x (2100 - 1970) years to cover leap years. A later filter can remove the spillover days
)
SELECT
DATEADD('DAY', ROW_NUMBER, DATE(0))::DATE as DATE -- Dimension starts on 1970-01-01 but a different value can be entered if desired - replace DATE(0) with '1900-01-01' to start at 1900 for example
  , EXTRACT(year FROM DATE) as  YEAR 
  , EXTRACT(month FROM DATE) as  MONTH 
  , EXTRACT(day FROM DATE) as  DAY 
  , EXTRACT(dayofweek FROM DATE) as DAY_OF_WEEK
  , EXTRACT(dayofyear FROM DATE) as DAY_OF_YEAR
  , EXTRACT(quarter FROM "DATE") as QUARTER
  , MIN("DAY_OF_YEAR") OVER (PARTITION BY "YEAR", "QUARTER") as "QUARTER_START_DAY_OF_YEAR"
  , "DAY_OF_YEAR" - "QUARTER_START_DAY_OF_YEAR" + 1 as "DAY_OF_QUARTER"
  , TO_VARCHAR("DATE", 'MMMM') as "MONTH_NAME"
  , TO_VARCHAR("DATE", 'MON') as "MONTH_NAME_SHORT"
  , CASE "DAY_OF_WEEK"
 WHEN 0 THEN 'Sunday'
 WHEN 1 THEN 'Monday'
 WHEN 2 THEN 'Tuesday'
 WHEN 3 THEN 'Wednesday'
 WHEN 4 THEN 'Thursday'
 WHEN 5 THEN 'Friday'
 WHEN 6 THEN 'Saturday'
END as "DAY_NAME"
  , CASE "DAY_OF_WEEK"
 WHEN 0 THEN TRUE
 WHEN 6 THEN TRUE
 ELSE FALSE END  as "IS_WEEKEND" 
  , TO_VARCHAR("DATE", 'DY') as "DAY_NAME_SHORT"
  , EXTRACT(yearofweekiso FROM "DATE") as "ISO_YEAR"
  , EXTRACT(weekiso FROM "DATE") as "ISO_WEEK"
  , CASE
  WHEN "ISO_WEEK" <= 13 THEN 1
  WHEN "ISO_WEEK" <= 26 THEN 2
  WHEN "ISO_WEEK" <= 39 THEN 3
  ELSE 4
END as "ISO_QUARTER"
   , EXTRACT(dayofweekiso FROM "DATE") as "ISO_DAY_OF_WEEK"
   , MAX("DAY_OF_YEAR") OVER (PARTITION BY "YEAR") as "DAYS_IN_YEAR"
   , "DAYS_IN_YEAR" - "DAY_OF_YEAR" as "DAYS_REMAINING_IN_YEAR"
FROM 
    GAPLESS_ROW_NUMBERS 
WHERE "YEAR" BETWEEN 1950 AND 2050  
GROUP BY  DAY_OF_YEAR,YEAR ,QUARTER ,GAPLESS_ROW_NUMBERS.ROW_NUMBER 
ORDER BY 1,2,3,4