1

I have query that uses the following:

STR_TO_DATE(JSON_EXTRACT_SCALAR(fb.p_dataforanalytics,'$.birthday'), '%m/%d/%Y'), 
STR_TO_DATE(JSON_EXTRACT_SCALAR(g.p_dataforanalytics,'$.birthday'), '%Y-%m-%d'),    
date_format(p_birthday, '%Y-%m-%d')

This syntax works in MySQL but it doesn't work in BigQuery. I somehow needs to convert it using PARSE_DATE(?) but I can't find the proper syntax for this.

EDIT:

PARSE_DATE('%m/%d/%Y', JSON_EXTRACT_SCALAR(fb.p_dataforanalytics,'$.birthday') ),
PARSE_DATE('%Y-%m-%d', JSON_EXTRACT_SCALAR(g.p_dataforanalytics,'$.birthday')),
PARSE_DATE('%Y-%m-%d', p_birthday)

Dosent work. I get:

Invalid result from parsing function

and also on the p_birthday row:

No matching signature for function PARSE_DATE for argument types: STRING, INT64. Supported signature: PARSE_DATE(STRING, STRING) at [15:33]

Luis
  • 1,305
  • 5
  • 19
  • 46

1 Answers1

0

Easy one, with standard SQL:

#standardSQL
SELECT PARSE_DATE('%m/%d/%Y',  '6/22/2017')


2017-06-22

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-date

JSON_EXTRACT_SCALAR should work unchanged.

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • I know that. But this doesn't work with my query. It's not just changing the function name. – Luis Feb 05 '19 at 10:32
  • I get Invalid result from parsing function – Luis Feb 05 '19 at 10:36
  • Looking at the edited question: Now you seem to have problems parsing the JSON data. Can you paste your JSON data in a new question? Also, can you paste the value of p_birthday? Seems to be a number and not a string. – Felipe Hoffa Feb 05 '19 at 10:43
  • some of the data may be invalid. How can I make sure that it can be converted to date and if not simply show NULL ? Basicly to PARSE_DATE if possible and if not return NULL – Luis Feb 05 '19 at 10:50