CODEWITHSUNDEEP
sqldategoogle-bigquery
Sebastian Kaczmarczyk
Sebastian Kaczmarczyk

Reputation: 25

How to get the first day of the previous month in SQL (BigQuery)

Would any of you know and would like to share the konwledge how to subtract the number of days from the current date (the data is type = DATE) so that I get the first day of the previous month. Here is an example:

Current Date = '2022-10-27' The date I want = '2022-09-01'

I know how to get the first day of the current month using this:

(CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE()) +1)

BuT I have no idea how to check how many days there were in the previous month and hence get the correct answer.

I though that maybe DATE_TRUNC(CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE())) would work but I'm getting this error: "No matching signature for function DATE_TRUNC for argument types: DATE"

SO that's clearly not the way. Any suggestions please? :)

Upvotes: 0

Views: 1260

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

Try using a combination of DATE_TRUNC and DATE_SUB as follows:

select current_date() as curr_date,
  date_sub(date_trunc(current_date(), MONTH), INTERVAL 1 MONTH) as lm_day_1

It produces the following:

enter image description here

Upvotes: 0

Related Questions