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   

Answer 2

First you need to "translate" you string representation of date into date type using PARSE_DATE() function and then DATE_DIFF() function allows you to calculate difference/distance between two dates as in example below

#standardSQL
SELECT 
  DATE_DIFF(
    PARSE_DATE('%Y%m%d', '20180220'), 
    PARSE_DATE('%Y%m%d', '20180115'), 
    DAY
  ) days

produces:

Row days     
1   36