Dateformat in Table Transformer

[Anders Johnsgaard]

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?

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

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:

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

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*

Hope this helps.

[Anders Johnsgaard]

Thanks @Katerina Kovriga _Stiltsoft_ !

Worked like a charm