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? :)

CodePudding user response：

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: