Convert string to date in table sql query

[Anders Kjöllerström]

Hi all!

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.

Example:

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!

Merry Christmas!

anders

CONVERT(DATETIME,'2021-12-12', 23) AS 'test',

returns:

Error: The CONVERT style 23 is not realized yet.

Parse('2021-12-12' AS DATE USING 'sv-SE') AS 'test',

returns:

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'

[Katerina Kovriga {Stiltsoft} · Accepted solution]

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.

[Anders Kjöllerström]

Hi Katerina!

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!

anders

[Katerina Kovriga {Stiltsoft}]

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.

[Katerina Kovriga {Stiltsoft}]

Hi there,

As this Community question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit modes.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.