BigQuery date function – find the first and the last day of month
Have date functions in BigQuery been challenging you while working? I will give you the easiest explanation on how to calculate any date in Google BigQuery efficiently and quickly.
Check out the video tutorial:
There are many various functions in BigQuery, and a lot of them are related to date! So, let’s begin with the basic one – the one I use a lot in my daily practice.
Finding the first and the last day of a current month – date_trunc function
In this article I will teach you how to apply the current_date formula to get the dates you need.
Current_date obviously means “today”, however while working with BigQuery or Google Sheets, we need to use exactly the current_date!
To apply the formula in BigQuery I used the date_trunc function, that will help us to distract a certain part of today’s date. This function is supposed to have 2 parameters, the current_date is not necessarily one of them – this parameter can be changed for other existing date fields in the table.
NOTE: you can always use BigQuery’s guide (date functions section) to read how to build the formulas in a correct way.
How to find the first day of current month in BigQuery
In BigQuery formulas have to begin with “Select”. Then there goes a title, and, consequently, the formula.
So, it will look like this:
select
“First day of current month” as a title,
date_trunc(current_date(), month) as date
The text in italics is the final version of the sql script, ready to use. What we did in this formula was:
- Creating a column called “title” and naming the first row as “First day of current month”
- Creating a column called “date” and, using the formula date_turnc(current_date(), month), we extracted the month from the date. Basically, if today is 15.03.2025, the extracted date from this formula will be 01.03.2025,.
How to find the last day of current month
The formula is a bit different, let’s take a look:
select
“Last day of current month” as a title,
date_trunc(date_add(current_date(), interval 1 month), month -1 as date
What’s going on with the date_trunc formula!?
Keep calm and let’s decompose it step by step:
- current_date()
Here we simply took the current date as a starting point.
- date_add(current_date(), interval 1 month)
Date_add formula adds a certain time interval to a date field. In this case, current_date is a date field, month is an added time interval. In fact, this exact formula will return us an exact day of the following month. (15.03.2025 → 15.04.2025)
- date_trunc(date_add(current_date(), interval month), month -1
Date_trunc function in combination of formulas we’ve used will return us the 1st of April. We only have to write -1 for our function to return us the last day of the current month! (31.03.2025)
Finding the first and the last day of the previous month – date_sub function
How to find the first day of the previous month?
This one will already be a bit easier for you.
select
“First day of previous month” as a title,
date_sub(date_trunc(current_date() month), interval 1 month) as date
What’s new here – a date_sub function. On the contrary to date_add, this formula will subtract a certain time period.
Still, it can be replaced by a date_add formula, using which we will have to deduct 1 month (interval -1 month) and followingly write -1 in the end, to get us the last day.
Similarly to this one, we can find the first day of the previous month.
How to find the last day of the previous month?
select
“Last day of previous month” as a title,
date_sub(date_trunc(current_date(), month), interval 1 day) as date
OR
select
“Last day of previous month” as a title,
date_trunc(current_date(), month), -1 as date
That’s much easier and shorter, do you agree?
Conclusion
Now you have a solid understanding of how to manipulate dates in BigQuery using date_trunc, date_add and date_sub functions, which will be a valuable skill for your data analysis tasks.
Hope this article was interesting and useful for you – don’t hesitate to leave your impressions about it in the comments section!