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; } } }

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',

...

Screenshot 2022-04-06 104109.png

Accepted answers

Katerina Kovriga _Stiltsoft_

Hi @Laurie Huth ,

Please try the following settings for the Table Transformer macro:

Wed 13-1.png

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

Wed 13-2.png

And use the following SQL query:

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

Wed 13-3.png

Hope this helps.

All comments

Katerina Kovriga _Stiltsoft_

Hi @Laurie Huth ,

Please try the following settings for the Table Transformer macro:

Wed 13-1.png

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

Wed 13-2.png

And use the following SQL query:

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

Wed 13-3.png

Hope this helps.

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.

Screenshot 2022-04-06 114656.png

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

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.

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

Welcome!

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

Quick Links

Find more posts tagged with

server

jira-software

jira-software-server