nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Katerina Kovriga {Stiltsoft}

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.

All comments

Katerina Kovriga {Stiltsoft}

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.

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.

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

sql

filter

table

date

gantt

table-filter

table-filter-and-charts

table-transformer