Snowflake get full day of week name

Sample data:

Trying to build an aggregate table at the weekday level. So there is a need to extract full day of week name from the dateHour column. dateHour column contains date and hour value of an event in it. It is like a timestamp column.

One way of extracting the full day name is using the below case when query. This solution works but it is taking a lot of time due to case statements. This is creating a bottle neck on the performance of the query.

select 
case dayname("dateHour"::date)
when 'Mon' then 'Monday'
when 'Tue' then 'Tuesday'
when 'Wed' then 'Wednesday'
when 'Thu' then 'Thursday'
when 'Fri' then 'Friday'
when 'Sat' then 'Saturday'
when 'Sun' then 'Sunday'
end as "day_of_week"
from tableName

Another query that I have tried is:

select TO_CHAR(CURRENT_DATE, 'DYDY') Day_Full_Name;

The above query works fine when CURRENT_DATE is used but when CURRENT_DATE is replaced with the column name dateHour from the table then it is giving short week day twice.
Something like this:

SunSun
MonMon
TueTue
WedWed
ThuThu

To replicate the DYDY issue check with the below code snippets:

with cte as (
select '2021-08-01 18:00:00.000'::timestamp  as "dateHour"
)
select 
"dateHour"::date as dt,
TO_CHAR(dt,'DYDY') day_full_name
from cte ;

The output from the above query:

with cte as (
select '2021-08-01 18:00:00.000'::timestamp  as "dateHour"
union all 
select '2021-08-02 20:00:00.000'::timestamp  as "dateHour"
)
select 
"dateHour"::date as dt,
TO_CHAR(dt,'DYDY') day_full_name
from cte 
;

Output:

Expected output (In the Output Sunday can be either of these: Sunday / SUNDAY )

I need an efficient way to generate the full week day name from the dateHour column. It shouldn't effect the performance of the aggregate query.

If performance is a problem, change the order of operations.

If you are doing large volumes of transforms date->string, then aggregation on strings, those two steps will be slower than if you aggregate on number (say dayofweek or date truncated value) and then convert to string at the in another select layer.

aka slower:

select 
TO_CHAR(dt, 'DYDY') as day_name,
sum(value) as sum_val
from big_table
group by day_name

verse faster:

select 
convert_to_name_step(dow) as day_name,
sum_val
from (
select 
dayofweek(dt) as dow,
sum(value) as sum_val
from big_table
group by dow
)

convertion bug:

with data as (
select 
column1 as dt
from values
('2021-08-01 18:00:00.000'::timestamp),
('2021-08-02 20:00:00.000'::timestamp),
(CURRENT_TIMESTAMP)
)
select 
dt
,SYSTEM$TYPEOF(dt) as t
,TO_CHAR(dt, 'DYDY') as n
,CURRENT_TIMESTAMP
,SYSTEM$TYPEOF(CURRENT_TIMESTAMP) as cd_t
,TO_CHAR(CURRENT_TIMESTAMP, 'DYDY') as cd_n
from data;

Shows it is not the data being passed to the function, but more the implementations appears to be different code paths.

I can reproduce the glitch with the union example, but couldn't find any documentation on the use of 'DYDY' to get day names. That said, an array-based approach might be faster than traversing the if else block

select current_timestamp as ct,
dayofweek(ct) as dow,
['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'][dow]::varchar