Dynamic Data Range Issue

1

Hello Community!

This is my first post and sorry if this is not the correct place to post. I’m attempting to write a more dremio friendly version of the statement below. Essentially using current date to get x months returned based on current date. I have failed to write a version that runs correctly in dremio. Any help you can provide will be greatly appreciated.

to_date([date field]) >= add_months(trunc(current_date(), ‘MM’),-1) and to_date([date field]) < add_months(trunc(current_date(), ‘MM’),0)

2

@Anthony.Lazzarini Add x months? Is that your requirement?

SELECT now(), date_add(now(), INTERVAL ‘6’ MONTH) plus_six_months

Using date_add you can also do the below

SELECT now(), date_add(now(), INTERVAL '10' YEAR) plus_ten_years from (VALUES 1)
SELECT now(), date_add(now(), INTERVAL '10' MONTH) plus_ten_months from (VALUES 1)
SELECT now(), date_add(now(), INTERVAL '10' DAY) plus_ten_days from (VALUES 1)
SELECT now(), date_add(now(), INTERVAL '10' HOUR) plus_ten_hours from (VALUES 1)
SELECT now(), date_add(now(), INTERVAL '10' MINUTE) plus_ten_minutes from (VALUES 1)
SELECT now(), date_add(now(), INTERVAL '10' SECOND) plus_ten_seconds from (VALUES 1)
1 Like
3

The goal was to capture the previous 1 month of results based on the current date but from the start of the previous month to the end no matter when it runs until the full month elapses.

I ended up using the following:
[date field] >= date_add(DATE_TRUNC(‘month’, CURRENT_DATE()), INTERVAL ‘-1’ MONTH) AND [date field] < DATE_TRUNC(‘month’, CURRENT_DATE())

Thank you for the help!

1 Like