Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Convert string to date in table sql query

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'

1 answer

1 accepted

Hi @Anders Kjöllerström ,

I assume you have the following case:

Wed 7-1.png

Wed 7-2.png

SELECT SUBSTRING(T1.'Page Title', 1, 10) AS 'Date', * FROM T1

Wed 7-3.png

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:

Wed 7-4.png

SELECT DATEDIFF(day, 'Date', "today") as 'Datediff',
* FROM T*

Wed 7-5.png

Wed 7-6.png

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:

Wed 7-7.png

Wed 7-8.png

Hope this helps your case.

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.Filtercase.png

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:

Filtercase2.png

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

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.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PERMISSIONS LEVEL
Site Admin
TAGS
Community showcase
Published in Confluence Cloud

🏠 Say hello to the new Confluence Home!

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

27,475 views 23 102
Read article

Community Events

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

Events near you