DATE_ADD function in BigQuery with examples

DATE_ADD function in BigQuery

DATE_ADD is a part of DATE function in BigQuery. It add a specified time interval to the DATE values. The following date_part values can be added to the DATE using DATE_ADD function.

  • DAY
  • WEEK – Equivalent to 7 DAY’s
  • MONTH
  • QUARTER
  • YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when we work with date in the below scenario.

  • Date is at (or near) the last day of the month
  • The resulting month has fewer days than the original date’s day,

In this example, we are adding 1 month to the date ‘2022-03-31‘. The month March has 31 days. But the next month April has 30 days. So the resulting date is the last date of April month 2022-04-30.

DATE_ADD function syntax

  • date_expression – It is an input date value or column.
  • INTERVAL – The keyword INTERVAL is required to add the date values.
  • int64_expression – It defines the number of intervals.
  • date_part – It specifies the date part values such as Day,Week,Month,Quarter and Year.

The return type of Date_Add function is a DATE value.

Example

Add days to current date

Let’s get the current_date value in BigQuery. The Select CURRENT_DATE returned the date as “2022-06-13

Get current date in BigQuery
Get current date in BigQuery

Now we will add specific days to current date using DATE_ADD function. As mentioned below, we passed the interval as 3 DAY. So the DATE_ADD function added 3 days to current date value.

Add days to current date in BigQuery
Add days to current date in BigQuery

Add days to specific date

In this example, we will add the days to specific date using DATE_ADD function.

Try to Execute

In the Date_add function, we mentioned the date expression as ‘2022-05-31’ and interval 2 Day. So it added 2 days to the input date and returned the output as “2022-06-02”

Date_add function example in BigQuery
Date_add function example in BigQuery

Add weeks to date in BigQuery

Let’s change the interval to 3 week in the Date_add function. It added 21 days (3 weeks) to the input date and returned the output as “2022-06-21”

Try to Execute

Add weeks to date in BigQuery
Add weeks to date in BigQuery

Add Month to date in BigQuery

Let’s add 1 month to the February month date “2022-02-28”. Since it is a last day of the month, DATE_ADD function added only 28 days to input date. The output date value is “2022-03-28”. As mentioned earlier, we need to handle this based on the requirement.

Add months to date in BigQuery
Add months to date in BigQuery

Add Quarter to date in BigQuery

In order to add 3 months to input date value, we have mentioned the Interval as 1 quarter in DATE_ADD function.

Adding quarter (3 months) to date in BigQuery
Adding quarter (3 months) to date in BigQuery

Add Year to date in BigQuery

This is for adding the years to the date value using DATE_ADD function. Here the interval is 2 year. So 2 years are added to the input date and returned the output as “2024-04-01“.

Add years to date in BigQuery
Add years to date in BigQuery