Big query error:Failed to parse input string

Question

Using Standard sql query but getting subject mentioned error(Failed to parse input string).

#standardSQL
SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_week,
  EXTRACT(WEEK FROM date) AS week_of_year,
  FORMAT_DATE("%y-%m", date) AS yyyymm
FROM( 
  SELECT PARSE_DATE('%y%m%d', date) date, campaign
  FROM `tech-team-staging-2019.DFW_GA_Data_v1_05122019.DFW_G_Analytics_Predicted_data_v1_05122019`
  GROUP BY 1,2
)

Please structure your code in a code block so it's more readable — Sean
– Sean, Commented Dec 5, 2019 at 23:31

Mikhail Berlyant · Accepted Answer · 2019-12-10 23:38:36Z

1

Below is for BigQuery Standard SQL

Note: it is not clear what your data field look like - so below are the options

in case if your date field is a string with YYYY-MM-DD - you should use below

#standardSQL
SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_week,
  EXTRACT(WEEK FROM date) AS week_of_year,
  FORMAT_DATE("%Y-%m", date) AS yyyymm
FROM( 
  SELECT PARSE_DATE('%Y-%m-%d', date) date, campaign
  FROM `tech-team-staging-2019.DFW_GA_Data_v1_05122019.DFW_G_Analytics_Predicted_data_v1_05122019`
  GROUP BY 1,2
)

in case if it is - YY-MM-DD

#standardSQL
SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_week,
  EXTRACT(WEEK FROM date) AS week_of_year,
  FORMAT_DATE("%Y-%m", date) AS yyyymm
FROM( 
  SELECT PARSE_DATE('%y-%m-%d', date) date, campaign
  FROM `tech-team-staging-2019.DFW_GA_Data_v1_05122019.DFW_G_Analytics_Predicted_data_v1_05122019`
  GROUP BY 1,2
)

finally, if it is YYMMDD

#standardSQL
SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_week,
  EXTRACT(WEEK FROM date) AS week_of_year,
  FORMAT_DATE("%Y-%m", date) AS yyyymm
FROM( 
  SELECT PARSE_DATE('%y%m%d', date) date, campaign
  FROM `tech-team-staging-2019.DFW_GA_Data_v1_05122019.DFW_G_Analytics_Predicted_data_v1_05122019`
  GROUP BY 1,2
)

and yet one more - YYYYMMDD

#standardSQL
SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_week,
  EXTRACT(WEEK FROM date) AS week_of_year,
  FORMAT_DATE("%Y-%m", date) AS yyyymm
FROM( 
  SELECT PARSE_DATE('%Y%m%d', date) date, campaign
  FROM `tech-team-staging-2019.DFW_GA_Data_v1_05122019.DFW_G_Analytics_Predicted_data_v1_05122019`
  GROUP BY 1,2
)

edited Dec 10, 2019 at 23:38

answered Dec 5, 2019 at 23:38

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bilal Over a year ago

thanks for your feedback all the queries are correct as mine but still facing same problem, getting same error message while running the queries. Moreover date format in schema is String Type (20191205). please share your experience with this.

Paddy Popeye Over a year ago

have you tried .... SELECT PARSE_DATE("%Y%m%d", "20191205") as parsed;

Collectives™ on Stack Overflow

Big query error:Failed to parse input string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related