Difference between two dates in dates using Google bigquery?

Question

How can I get the difference in days between 2 timestamp fields in Google Big Query?

The only function I know is Datediff which only works in Legacy SQL but I'm in Standard SQL.

For example: the difference between 20180115 to 20180220 is 36 days.

Answer 1

As per documentation, you should use DATE_DIFF function in standard SQL.

Your query should look like this:

SELECT DATE_DIFF(DATE '2018-02-20', DATE '2018-01-15', DAY) as days_diff;

result:

Row days_diff    
 1     36