How to get Day name in Google BigQuery

Question

How to get the name of the day from date in Google BigQuery.

I can achieve this by below query

SELECT CASE WHEN DAYOFWEEK(CURRENT_DATE())=1 THEN 'Sunday' WHEN DAYOFWEEK(CURRENT_DATE())=2 THEN 'Monday'
             WHEN DAYOFWEEK(CURRENT_DATE())=3 THEN 'Tuesday' WHEN DAYOFWEEK(CURRENT_DATE())=4 THEN 'Wednesday'
             WHEN DAYOFWEEK(CURRENT_DATE())=5 THEN 'Thusday' WHEN DAYOFWEEK(CURRENT_DATE())=6 THEN 'Friday'
             WHEN DAYOFWEEK(CURRENT_DATE())=7 THEN 'Saturday' END as [DOW]

If there is any default function available to get name of the day?

Answer 1

Yes, there are equivalent functions in both, standardSQL and legacySQL.

Standard SQL

You can use the FORMAT_DATE() function where you can use any date format you'd like. Link to Docs

Example:

#standardSQL
SELECT
  CURRENT_DATE() AS date,
  FORMAT_DATE('%A', CURRENT_DATE()) AS dow

Legacy SQL

There is the STRFTIME_UTC_USEC() function. It requires converting your timestamp to USEC first though. Link to docs

Example:

#legacySQL
SELECT
  CURRENT_DATE() AS date,
  STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(TIMESTAMP(CURRENT_DATE())), '%A') AS dow

Answer 2

In Google's Big Query, the function to use is: format_datetime('%A',yourdate) instead of format_date as posted in 2017.