I have a table with public holidays for the next couple of years. As I want to display future public holidays for a certain time range only, I'm trying (and crying) to create a SELECT statement which returns all dates from today to six months in the future.
With this SELECT I can display all rows from today:
WHEN 6 - WEEKDAY('date') = 2 OR WEEKDAY('date') - 1 = 1 THEN CONCAT('name', " + ", "Brückentag")
END AS 'Feiertag', 'date' AS 'Datum', 'scope' AS 'Geltungsbereich', WEEKDAY('date') AS 'Wochentag'
WHERE CAST('date' AS DATE) > GETDATE()
ORDER BY 'date' ASC
To get just the dates for the coming six months, I tried, amongst others, the following solutions which all returned 0 rows:
I also tried to find all rows until the end of the current year but this only worked with a hardcoded "2023-01-01".
Any ideas for an nice solution?
Hi @Eishle ,
I can suggest using the Table Filter macro for the case:
Make sure that your date format corresponds the format that you are using in your source table:
You can use various expressions (-6m, 1w, today, etc.) and hide the filtration panel if necessary.
If your original case is a more complex one and you still need help, please raise a support ticket. The portal is confidential, so provide us with the screenshots of the source tables (with visible headers) and macro settings and we'll try to help you.
Thanks for your quick reply. If I set the range with this macro, would it serve as a "preset" for other page members or would they need to set it by themselves?
The use case is not complicated. That's why I was surprised that my solution didn't work.
At the moment, I use "LIMIT" in combination with "WHERE CAST('date' AS DATE) > GETDATE()" which is not perfect but comes quite close to the wanted effect.
If you save this filter, it will be set for all the page visitors (it works like any other simple dropdown or date range filter).
The "3m" and other dynamic filters were introduced specifically for the cases that are similar to yours (when there are manually created and automatically generated by the Page Properties Report, Jira Issues, etc. tables that need to show up-to-date information without any routine manual updates).
Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event