Home » Subtracting dates in BigQuery SQL

# Subtracting dates in BigQuery SQL

In this blog post, I elaborate on two specific operations you’ll run into when using dates in BigQuery. Subtracting an interval from a data and calculating the difference between two dates.

## Subtracting a unit

Subtracting a specific amount of days, weeks, months, quarters, or years from a date can be done using the DATE_SUB function. The first argument takes a date and the second argument takes an interval, a numeric value, and a unit. The supported units (DATE_PART) are:

• Days: DAY
• Weeks: WEEK
• Months: MONTH
• Quarters: QUARTER
• Years: YEAR

Here’s an example on how to do it:

SELECT DATE_SUB('2020-01-28', INTERVAL 6 DAY) as six_days_ago

This is fairly different from (for example) MySQL, where you can simply use mathematical operators on dates using an INTERVAL.

## Subtracting two dates (Difference)

Subtracting two dates from each other (i.e. calculating the difference) uses the DATE_DIFF function, where the second argument is subtracted from the first argument. The third argument is the unit (DATE_PART) in which you’d like to receive the calculation.

Supported units:

• Days: DAY
• Weeks: WEEK
• Specific weekdays: WEEK(…) e.g. WEEK(SUNDAY)
• Weeks, starting on monday: ISOWEEK
• Months: MONTH
• Quarters: QUARTER
• Years: YEAR
• First mondays of the year: ISOYEAR

This is a simple example that calculates the difference between two dates:

SELECT DATE_DIFF('2020-01-28', '2019-08-28', DAY) as days_in_between

This is how to subtract dates in BigQuery.

### Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.