Column Styles not reformatting dates from SQL Query...

[Niall Poole]

Hi,

I'm using SQL to pull a list of milestones from a 3rd party system - the returned table has three columns - Project, Milestone, Date.

I using table excerpt to pull other tables from around confluence with project phase dates and trying to put the whole thing in a Gantt chart (Chart from Table macro).

Problem: The dates in the SQL query are in a different format to the dates in the other tables. The Gantt chart is showing the dates from the other tables, but ignoring the rows with the different date format. So I think I need to get all the dates in the same format and I don't have control of the other confluence pages so I'm looking at the SQL query...

The date format in the SQL table is yy-mm-dd (2022-11-01). The confluence tables use the date macro - I think the format is dd M yy (01 Nov 2022).

I've tried using column types and styles in the SQL Query macro but I can't seem to get it to do anything...

columnTypes = S, S, M

columnStyles = ,,MYYYY-MM-DD~DDMMMYYYY

Am I using it wrong? Or is there a better way to do it?

Niall.

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Niall Poole ,

As you've mentioned the Chart from Table macro, I assume that you use our Table Filter and Charts from Confluence app.

So, to change the date format from the tables coming from Confluence, you may also use our Table Transformer macro.

For example, you wrap the source table in the Table Excerpt macro, reuse it on the required page with Gantt chart via the Table Excerpt Include macro, then use the Table Transformer macro on top to change date format and then continue building your chart.

The query for your case will be as following:

SELECT T1.'Phase', T1.'Date',
T1.'Date'::Date->toLocaleDateString("sv-SE", {year:"numeric", month:"2-digit", day:"2-digit"}) AS 'Converted Date'
FROM T*

Hope this helps your case.

[Niall Poole]

Thanks @Katerina Kovriga {Stiltsoft} 

That might work - I should get all the dates in the same format and then it's just the setting in the Chart from Table macro to tell it what format to expect if I remember rightly..., I'll give it a go.

Out of interest, is it possible to do the conversion the other way round? (Only because it would look nicer in the table :) and, in my scenario, fewer places to change it)

Niall.

[Katerina Kovriga {Stiltsoft}]

Yes, it will be even simpler than the first variant: go to the Settings tab of your Table Transformer macro and set the d M yy variant of the date format (it corresponds to your "28 Apr 2016" date picker, I assume).

Then use the following query:

SELECT T1.'Phase', FORMATDATE(T1.'Date') AS 'Converted Date'
FROM T*

The "2016-04-16" will turn as "28 Apr 2016".

[Katerina Kovriga {Stiltsoft}]

You may also use the FORMATDATE(T1.'Date', "d M yy") function with additional parameter "d M yy" that is your desired output to convert various date formats. This option is more easy-to-read than the first one.

And you may try to set the yy-mm-dd date format in the Chart from Table macro (at once, without any transformations) and the d M yy format will be understood automatically by the macro. But not vice versa or if you have other possible date formats in your source tables.

[Niall Poole]

Brilliant @Katerina Kovriga {Stiltsoft} 

The FORMATDATE command has done the trick - I set the format in the macro, as you describe. rather than an extra parameter in the command.

I just need to get back to Alexey on this issue...

https://community.atlassian.com/t5/Marketplace-Apps-Integrations/Stop-Events-showing-as-Milestones/qaq-p/2089460

...and my Gannt chart will be perfect :)

Niall.