CODEWITHSUNDEEP
sqltimestampgoogle-bigquerydayofweek
shayms8
shayms8

Reputation: 771

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

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 :)

Upvotes: 11

Views: 54771

Answers (3)

Yannick Pezeu
Yannick Pezeu

Reputation: 808

In BigQuery you can use:

SELECT

EXTRACT(DAYOFWEEK
  FROM
    {your_timestamp}) AS dayofweek,

FROM TABLE

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

Upvotes: 11

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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

Upvotes: 28

Related Questions