DATE_DIFF Syntax in Google Bigquery

Use the DATE DIFF function to subtract two days and get the difference

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

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

The DATE_DIFF function subtracts the second parameter from first parameter. 

You can also get the difference in,

• DAY
• WEEK This date part begins on Sunday
• MONTH
• QUARTER
• YEAR

You can extend this function to get:

SELECT 
DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_between,
DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', WEEK) as weeks_between,
DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', MONTH) as months_between,
DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', QUARTER) as quarters_between,
DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', YEAR) as years_between;

nVector

posted on 22 Sep 19