Previous month query - Databricks

Question

I try to find a function where I can extract the result of the last month only (for exemple if I launch the query in november, I want to display only the resultat of october)

There the result :

I dont know if I have to enter the function in my select or where clause

Thanks for you help!! CHeers!

I tried the function month(date, -1)

I want to see all the result for the previous month

score 0 · Answer 1 · answered Nov 23 '22 at 08:31

You can try getting the date and applying in the where clause.

Note that there may be more efficient options available.

Query to Use:

WHERE 
DATE_TRANSACTION BETWEEN 
trunc(date_sub(CURRENT_DATE, dayofmonth(CURRENT_DATE)),'MM') 
AND 
date_sub(CURRENT_DATE, dayofmonth(CURRENT_DATE))

Explanation:

CURRENT_DATE - Gives the current Date
dayofmonth(CURRENT_DATE) - Gives the day part of current date.
date_sub(CURRENT_DATE, dayofmonth(CURRENT_DATE)) - Gives the last day of previous month. Assume current_date is 2022-11-23, dayofmonth will give 23, when you subtract 23 days, it goes to the last day of previous month i.e. 2022-10-31.
trunc(date_sub(CURRENT_DATE, dayofmonth(CURRENT_DATE)),'MM') - Truncates date to first day of month.
DATE_TRANSACTION - between these two days.

Previous month query - Databricks

1 Answers1