Convert date format in table

[Laurie Huth]

Issue: I am using the Jira Issue/Filter macro in a Confluence page (wrapped in a few other macros - see below). The date pulls in from Jira as: Month Day, Year (example: April 04, 2022). However, I want to change it so it appears as Day Month, Year in the Confluence table. I'm not sure what else I need to add.

Here is my SQL.

SELECT 'Key',

FORMATWIKI(SUM('Due')) AS 'Expected Impact Date',

...

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Laurie Huth ,

Please try the following settings for the Table Transformer macro:

Set the required Date Format that you want to see in the result table:

And use the following SQL query:

SELECT T1.'Key',
FORMATDATE(T1.'Due') AS 'Expected Impact Date'
FROM T1

Hope this helps.

[Laurie Huth]

Thank you! How does this work if you have many more lines of SQL query? The date goes blank if I add more to this query.

[Laurie Huth]

Thank you! That works perfectly. I can see the date modified like I wanted. You are awesome!

However, I notice when I add my other lines to this SQL query, the date disappears. This happens even if I add just one more line, or all of them.

Here is the very long script I had originally:

SELECT 'Key',

FORMATWIKI(SUM('Due')) AS 'Expected Impact Date',

FORMATWIKI(SUM('Summary' + "\n" + 'Additional Info')) AS 'Change Details',

FORMATWIKI(SUM('Product')) AS 'Product',

FORMATWIKI(SUM('Region Impact')) AS 'Region Impact',

FORMATWIKI(SUM('Impact Analysis')) AS 'Change Impact',

FORMATWIKI(SUM("External: " + 'External Document' + "\n" + "Internal: " + 'Internal Document')) AS 'Resources',

FORMATWIKI(SUM('Business Area')) AS 'Impacted Business Areas',

FORMATWIKI(SUM('Customer Business Impact')) AS 'Customer Business Impact'

FROM (

SELECT *,

CASE

WHEN 'Additional Info' IS NULL

THEN "No Additional Info"

ELSE 'Additional Info'

END

AS 'Additional Info'

FROM T*)

GROUP BY 'Key'

ORDER By 'Expected Impact Date' ASC

[Katerina Kovriga _Stiltsoft_]

Change the highlighted line to

FORMATWIKI(SUM(FORMATDATE('Due'))) AS 'Expected Impact Date',

It should work this way.

[Laurie Huth]

Katerina -

Thank you, thank you! You are such a rock star!! I really appreciate all the solutions you contribute.

Virtual hugs to you,

Laurie