snowflake-cloud-data-platformteradata-sql-assistantteradatasql

Alternative for Sys_Calendar of teradata in snowflake


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.]


Solution

  • 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