BigQuery DATE_DIFF Error: Encountered " <STRING_LITERAL>

Question

I'm trying the following query from the BigQuery Standard SQL documentation:

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#date_diff

However, I'm receiving the following error from the UI:

Error: Encountered " "\'2010-07-07\' "" at line 1, column 23. Was expecting: ")" ... [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

This is a simple copy and paste from the doc into the web UI Query Editor.

Any idea on how to resolve this?

Answer 1

Although the answer has already been provided in the comments to your questions and by Mikhail in the other answer, let me share with you a complete answer that hopefully addresses all your doubts:

ERROR MESSAGE

As explained in the error message you are getting, [Try using standard SQL (...)]. You are trying to run this sample using Legacy SQL (which instead would use the DATEDIFF function). You are actually right, you are running the exact same query provided in the documentation, but the issue here is that the documentation you are using is for Standard SQL (the preferred query language in BigQuery), but you are instead using Legacy SQL (the default language in the old UI, the one you are using).

CHANGE THE QUERY LANGUAGE IN USE

First of all, I would like to remark the importance of using Standard SQL instead of Legacy SQL, as the former adds new functionalities and is the current recommended language to use with BigQuery. You can see the whole list of comparisons in the documentation, but if you are starting with BigQuery, I would just go straight away with Standard SQL.

Now, that being clarified, in order to use Standard SQL instead of Legacy SQL, you can have a look at the documentation here, but let me summarize the available options for you:

• In the BigQuery UI, you can toggle the Use legacy SQL option inside the Show options menu. If this option is marked, you will be using Legacy SQL; and if it is not, you will be using Standard SQL.
• You can use a prefix in your query, like #standardSQL or #legacySQL, which would ignore the default configuration and use the language you specify with this option. As an example on how to use it, please have a look at the other answer by Mikhail, who shared with you a couple of examples using prefixes to identify the language in use. You should copy the complete query (including the prefix) in the UI, and you will see that it works successfully.
• Finally, as suggested by Elliott, you can use the new UI, which has just recently released in Beta access. You can access it through this link https://console.cloud.google.com/bigquery instead of the old link https://bigquery.cloud.google.com that you were using until now. You can find more information about the new BigQuery Web UI in this other linked page too.

Answer 2

Below are examples for respectively BigQuery Legacy SQL and Standard SQL

Make sure you try code as it is in answer below - not just second lines but 2(both) lines including first line that looks like comment - but in reality important part of query as it controls which SQL dialect will be in effect!

#legacySQL
SELECT DATEDIFF(DATE('2010-07-07'), DATE('2008-12-25')) AS days_diff

and

#standardSQL
SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) AS days_diff   

both returns result as below

Row days_diff    
1   559     

Ideally, you should consider migrating to Standard SQL