22 Sep 19 ·  nVector in Bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds