datesnowflake-cloud-data-platformsnowflake-schemasnowflake-task

get date day full name in snowflake

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

Solution

  • 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)];