I'm using a Table Transformer Macro to create a table where one column is the the first ten characters of the title string, which represent a date.
2021-12-12 Super Important Meeting returns 2021-12-12
This part works fine. My remaining issue is that the result is not defined as date format..
I tried using both CONVERT and PARSE with a fixed date rather than the expression above, but this does not work out.
Any help is much appreciated!
CONVERT(DATETIME,'2021-12-12', 23) AS 'test',
Error: The CONVERT style 23 is not realized yet.
Parse('2021-12-12' AS DATE USING 'sv-SE') AS 'test',
SyntaxError: Parse error on line 1: ... Parse(`2021-12-21` AS DATE USING `sv-SE -----------------------^ Expecting 'EOF', 'COMMA', 'RPAR', 'IN', 'LIKE', 'ARROW', 'DOT', 'CARET', 'EQ', 'SLASH', 'EXCLAMATION', 'MODULO', 'GT', 'LT', 'GTGT', 'LTLT', 'NOT', 'AND', 'OR', 'PLUS', 'STAR', 'RBRA', 'END', 'ELSE', 'REGEXP', 'TILDA', 'GLOB', 'NOT_LIKE', 'BARBAR', 'MINUS', 'AMPERSAND', 'BAR', 'GE', 'LE', 'EQEQ', 'EQEQEQ', 'NE', 'NEEQEQ', 'NEEQEQEQ', 'BETWEEN', 'NOT_BETWEEN', 'IS', 'DOUBLECOLON', 'GO', 'SEMICOLON', got 'AS'
Hi @Anders Kjöllerström ,
I assume you have the following case:
SELECT SUBSTRING(T1.'Page Title', 1, 10) AS 'Date', * FROM T1
Now your date is a string and the simplest way to perform any manipulations with it as a real date is to use another Table Transformer macro with a correctly specified date format:
SELECT DATEDIFF(day, 'Date', "today") as 'Datediff',
* FROM T*
And if you want to use other macros on top of the first Table Transformer macro, then you also don't need to convert the dates, just choose an appropriate date format in the macro settings.
Here is an example with the Pivot macro:
Hope this helps your case.
Thanks for helping out (again)!
Yes, your assumption of the start of my case is correct, but my issue is slightly different.
My problem was that when applying a filter to the column, the date column did not respond to filtering, I assumed wrongly that this was because it was not defined as date format.
While playing around with it a bit further, I've found the solution. Putting it here for fellow rookies :)
In the picture you can see that the date format is different in the date column and the date filter. I just had to change the date filter format to correspond to the column format. It can be done temporarily directly in the user view of the table or (better) permanently here:
I'm still in the dark as to why the filter differentiate the different types of date format. To me they all represent the same date value in the end. This seems to be more of a fundamental difference from my previous experience (i'm old), maybe it is just an effect of trying to make the administration more user friendly and not relying on administrators having coding experience? Could you @Katerina Kovriga _Stiltsoft_ or anyone else point to some reading explaining this on a dummy level?
Anyway, main problem solved, thanks!
Here is our documentation about the dates' formats and their correct settings inside our macros.
You need to always check the specified date format - our macros "remember" the last used format and when you insert another macro on the page, the date format copies the previous use case.
Why do you need to specify the date format: for our macros to work correctly using the calendar. For example, the same date as 2021-12-01 could be 01 Dec 2021 or 12 Jan 2021. And it is important while filtering, aggregating by periods, counting datediffs, etc.
Hi Atlassian Community, My name is DJ Chung, and I’m a Product Manager on the Confluence Cloud team. Today, I’m excited to share a new and improved version of Home. The new Home helps you ...
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
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events