CODEWITHSUNDEEP
google-bigquery
user20184534
user20184534

Reputation: 41

How to extract year and change format to mm-dd-yyyy from yyyy/mm/dd in BigQuery

Not able to extract year from this format(yyyy/mm/dd) and not able to change the format to mm-dd-yyyy . I have tried the following but failed.

with table1 as(
select "2017/1/1" as doj union all
select "2017/2/3" as doj union all
select "2017/3/5" as doj union all
select "2017/4/7" as doj union all
select "2017/6/9" as doj 
)
select format_date("%m-%d-%Y",doj)doj from table1


Error: No matching signature for function FORMAT_DATE for argument types: STRING, STRING. Supported signature: FORMAT_DATE(STRING, DATE)

Upvotes: 0

Views: 321

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500903

You need to use parse_date first to parse, before you can format or extract the year. This works:

with table1 as(
  select "2017/1/1" as doj union all
  select "2017/2/3" as doj union all
  select "2017/3/5" as doj union all
  select "2017/4/7" as doj union all
  select "2017/6/9" as doj 
)
select
   format_date("%m-%d-%Y", parse_date("%Y/%m/%d", doj)) as doj,
   extract(YEAR from parse_date("%Y/%m/%d", doj)) as year
from table1

Although depending on what you're doing with the result, I'd suggest just parsing, and returning the date to the client to extract the year and reformat if they need to. I understand there are situations where that's not feasible, e.g. if you need to write a table for some other client which really needs the date as a string in a particular format.

If you need to format the date but get to choose the format at all though, I'd definitely use ISO-8601 rather than month-first or day-first :)

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table1` AS(
  SELECT "2017/1/1" AS doj UNION ALL
  SELECT "2017/2/3" AS doj UNION ALL
  SELECT "2017/3/5" AS doj UNION ALL
  SELECT "2017/4/7" AS doj UNION ALL
  SELECT "2017/6/9" AS doj 
)
SELECT 
  PARSE_DATE("%Y/%m/%d", doj) doj_as_date,
  EXTRACT(YEAR FROM PARSE_DATE("%Y/%m/%d", doj)) year
FROM `project.dataset.table1`

Upvotes: 2

Related Questions