nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer: How to get all rows within a certain time range starting from today?

Eishle

Hi,

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:

SELECT
CASE
WHEN 6 - WEEKDAY('date') = 2 OR WEEKDAY('date') - 1 = 1 THEN CONCAT('name', " + ", "Brückentag")
ELSE name
END AS 'Feiertag', 'date' AS 'Datum', 'scope' AS 'Geltungsbereich', WEEKDAY('date') AS 'Wochentag'
FROM T1
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:

WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATEADD(month, -6, getdate())
WHERE CAST('date' AS DATE) BETWEEN GETDATE() AND DATE_ADD(GETDATE(), INTERVAL 6 MONTH)
WHERE 'date' < DATEADD(month, 6, GETDATE())
WHERE CAST('date' AS DATE) < DATEADD(month, 6, GETDATE())

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?

Thanks,

Eishle

Accepted answers

Katerina Kovriga {Stiltsoft}

Hi @Eishle ,

I can suggest using the Table Filter macro for the case:

Mon 6-1.png

Mon 6-2.png

Make sure that your date format corresponds the format that you are using in your source table:

Mon 6-3.png

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.

All comments

Katerina Kovriga {Stiltsoft}

Hi @Eishle ,

I can suggest using the Table Filter macro for the case:

Mon 6-1.png

Mon 6-2.png

Make sure that your date format corresponds the format that you are using in your source table:

Mon 6-3.png

You can use various expressions (-6m, 1w, today, etc.) and hide the filtration panel if necessary.

Eishle

Hi @Katerina Kovriga _Stiltsoft_,

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.

Katerina Kovriga {Stiltsoft}

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).

Katerina Kovriga {Stiltsoft}

And what concerns the Table Transformer macro, it is based on the AlaSQL library. So some functions may be limited and require a workaround.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

table-transformer