Dynamic Date Conditions in Dremio Query

1

Hi team,
I am new to dremio.I am currently working with Dremio and trying to incorporate dynamic date conditions in my query. However, I am encountering an error with the DATE_FORMAT function. Specifically, the conditions I am attempting to use are as follows
DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,‘%Y-%m-01’)
and
DATE_FORMAT(LAST_DAY(now() - INTERVAL 1 MONTH),‘%Y-%m-%d’)

Dremio verison : 24.3.2-202401241821100032-d2d8a497

Could you please provide the corresponding functions in Dremio to achieve the above use case?

2

Will the following help ?

SELECT TO_DATE(DATE_SUB(CURRENT_DATE(), CAST(1 AS INTERVAL MONTH)), ‘YYYY-MM-DD’,0)

SELECT TO_DATE(DATE_SUB(LAST_DAY(now()), CAST(1 AS INTERVAL MONTH)), ‘YYYY-MM-DD’,0)

Ref:

3

Thanks for the information @Simon_Ashley.

I am getting same result as “2024-04-30” from the 2 queries you shared. I want to dynamically retrieve the first date of the previous month(“2024-04-01”) in Dremio. How can I achieve this?

Equalent date condition for DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,‘%Y-%m-01’) in dremio.

4

There is no explicit FIRST_DAY function but you could use LAST_DAY and DATE_SUB to step back 2 months and then use DATE_ADD to add 1 day to give you the first day of the previous month, like so

SELECT TO_DATE(DATE_ADD(DATE_SUB(LAST_DAY(now()), CAST(2 AS INTERVAL MONTH)),1) , ‘YYYY-MM-DD’,0)

I’m sure there are other ways to do this but hopefully this gives you the general idea

5

Hi @Simon_Ashley Thanks for the update.

I am trying to calculate the last days of May and March using the logic you provided. However, instead of getting ‘2024-05-31’ and ‘2024-03-31’, the results are ‘2024-05-30’ and ‘2024-03-30’. Could you please help me correct this?

Here are the queries I am using:

Calcuating last day of May : SELECT TO_DATE(DATE_SUB(LAST_DAY(now()), CAST(1 AS INTERVAL MONTH)), ‘YYYY-MM-DD’,0)

Calcuating last day of March : SELECT TO_DATE(DATE_SUB(LAST_DAY(now()), CAST(3 AS INTERVAL MONTH)), ‘YYYY-MM-DD’,0)

Thanks in Advance

6

Ok,it looks like my logic was slightly incorrect, try the following instead

May: SELECT LAST_DAY(DATE_SUB(now(), CAST(1 AS INTERVAL MONTH)))

March: SELECT LAST_DAY(DATE_SUB(now(), CAST(3 AS INTERVAL MONTH)))