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,295,088
Community Members
 
Community Events
165
Community Groups

Dateformat in Table Transformer

Edited

Hi,

 

We are trying to merge several tables from different spaces using table excerpts and normal manual tables, but we struggle to unify the date format.

Is the format dd.mm.yyyy (e.g. 01.06.2022) not acceptable by the Table Transformer? Or is this an issue caused by the different profile-languages set per user in confluence? 

Anyways, here is my scenario:

 

I get several tables, everyone with the same columns, but with different input format of the columns "Start date" and "Due date".

Some dates are written as "dd.mm.yyyy", some are "M dd, yy" (Jira), others are "dd M yy" (Confluence "//" macro)

I've been trying to FORMATDATE(Tx.'Date') each of them into "M dd, yy".

  • "dd M yy"  is not transformed at all.
  • "dd.mm.yyyy" is transformed into "M dd, yy". but it screws up the day and month order
    • Making 01.06.2022 (which is 1st of June) into "Jan 06, 2022".
    • And 28.06.2022 is then "undefined NaN NaN"

 

Is there any settings in Confluence, or other workarounds when working with different dateformats to unify it no matter what profile language or browser settings or what ever?

1 answer

1 accepted

Hi @Anders Johnsgaard ,

We can suggest using the following workaround to convert different dates to the Jira format with the help of the Table Transformer macro:

Fri 7-1.png

Set the Date Format as "dd.mm.yy":

Fri 7-3.png

And use the following SQL query:

SELECT T1.'Date' AS 'Original Date', T1.'Date'::Date->toLocaleDateString("en-us", { year:"numeric", month:"short", day:"2-digit"}) AS 'Converted Date' FROM T*

Fri 7-2.png

Hope this helps.

Thanks @Katerina Kovriga _Stiltsoft_ !

Worked like a charm

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

863 views 11 26
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you