Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,366,447
Community Members
 
Community Events
168
Community Groups

Column Styles not reformatting dates from SQL Query...

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.

1 answer

1 accepted

5 votes
Answer accepted

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*

Thu 6-1.png

Hope this helps your case.

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.

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

Like # people like this

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.

Like # people like this

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.

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events