BigQuery Standart SQL: extract day-of-week name from timestamp

Question

I'm Using BigQuery Standart SQL.

I need to convert a timestamp to Day-of-week name.

e.g. convert today's timestamp (2016-11-24 00:00:00) into a string: 'Thursday'

Thanks :)

score 20 · Accepted Answer · answered Apr 25 '17 at 18:53

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  CURRENT_DATE() AS day,
  FORMAT_DATE('%a', CURRENT_DATE()) AS weekday_name_abbreviated,
  FORMAT_DATE('%A', CURRENT_DATE()) AS weekday_name_full

or

#standardSQL
SELECT 
  DATE('2016-11-24 00:00:00') AS day,
  FORMAT_DATE('%a', DATE('2016-11-24 00:00:00')) AS weekday_name_abbreviated,
  FORMAT_DATE('%A', DATE('2016-11-24 00:00:00')) AS weekday_name_full

result is

day         weekday_name_abbreviated    weekday_name_full    
2016-11-24  Thu                         Thursday

score 6 · Answer 2 · edited Jul 07 '23 at 21:24

6

In BigQuery you can use:

SELECT

EXTRACT(DAYOFWEEK
  FROM
    {your_timestamp}) AS dayofweek,

FROM TABLE

It returns an integer: 1 = Sunday, 7 = Saturday

edited Jul 07 '23 at 21:24

Vitali Kaspler

1,340
10
16

answered May 22 '21 at 23:10

Yannick Pezeu

530
1
7
12

> DAYOFWEEK: Returns values in the range [1,7] with Sunday as the first day of of the week. https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract – Smedegaard Mar 30 '22 at 09:44

Daryl Wenman-Bateson · Answer 3 · 2017-08-01T14:13:37.977

0

You have to use DAYOFWEEK() and then a CASE statement to return day of week. DAYOFWEEK() returns the day of the week as an integer between 1 (Sunday) and 7 (Saturday). Date and time functions

e.g.

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 'Thursday'
    WHEN DAYOFWEEK(CURRENT_DATE()) = 6 THEN 'Friday'
    WHEN DAYOFWEEK(CURRENT_DATE()) = 7 THEN 'Saturday'
  END Weekday

edited Aug 01 '17 at 14:13

answered Nov 26 '16 at 19:16

Daryl Wenman-Bateson

3,870
1
20
37

Yep i figured this way already. It works but looks pretty ugly :P Thanks anyway! – shayms8 Nov 27 '16 at 15:37
1

@mav3n - this **DOES NOT** work for BigQuery Standard SQL. you should correct your Edit to this answer – Mikhail Berlyant Jul 21 '17 at 13:14
@MikhailBerlyant : Standard SQL have backward compatibility to Legacy SQL right? – Sahil Chhabra Jul 23 '17 at 10:38
1

@mav3n - nope - no backward compatibility! – Mikhail Berlyant Jul 23 '17 at 14:04

BigQuery Standart SQL: extract day-of-week name from timestamp

3 Answers3