DATE_ADD function in BigQuery with examples
Contents
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,
|
1 2 3 |
Example: DATE_ADD('2022-03-31', INTERVAL 1 month) Output : 2022-04-30 |
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
|
1 |
DATE_ADD(date_expression, INTERVAL int64_expression date_part) |
- 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“
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 specific date
In this example, we will add the days to specific date using DATE_ADD function.
|
1 |
SELECT DATE_ADD('2022-05-31', INTERVAL 2 DAY) as two_days_later; |
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”
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”
|
1 |
SELECT DATE_ADD('2022-05-31', INTERVAL 3 week) as three_weeks_later; |
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 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.
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“.
Recommended Articles
