'How to get Day name in Google BigQuery
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?
Solution 1:[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
Solution 2:[2]
In Google's Big Query, the function to use is: format_datetime('%A',yourdate) instead of format_date as posted in 2017.
Solution 3:[3]
As mentioned by Medur Mamutov above. One can leverage the format_date function and leverage the different formatting elements.
For the full weekday name try:
SELECT FORMAT_DATE("%A", CURRENT_DATE())
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tomas |
| Solution 2 | Meder Mamutov |
| Solution 3 | nReyna |