get date day full name in snowflake

Question

get date day full name in snowflake

I tried to use below query to get day name but giving short name only(3 Chars of day)

SELECT upper(trim(DAYNAME(CURRENT_DATE)))

O/p: MON

Expected output: MONDAY

Answer 1

From the Snowflake date/time examples:

select decode(extract ('dayofweek_iso',current_date()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday');

Alternatively:

select decode(DAYNAME(CURRENT_DATE), 'Mon','Monday', 'Tues','Tuesday', ...);

select object_construct('Mon','Monday', 'Tues','Tuesday', ...)[DAYNAME(CURRENT_DATE)];

Answer 2

Here's a more concise alternative approach:

SELECT CURRENT_DATE Today, UPPER(TO_CHAR(CURRENT_DATE, 'DYDY')) Day_Full_Name

Output:

|TODAY        |DAY_FULL_NAME |
|2023-06-30   |FRIDAY        |