How to Handle Dates that are Strings "Thursday 4:14 PM"

Question

We are using the table transform to join two tables together and then using the FORMATWIKI function to identify all issues that have been updated in the last 3 days and coloring the text to easily identify them.

The issue im running into is when an issue has recently been updated, it doesn't return the date, but a string i.e. "Thursday 4:14 PM" so when formatting the date fields to do a compare of now and the updated time stamp, it returns "NaN".

Is there a good way to handle these?

The following will not evaluate correctly as it sees the updated date as a string

SELECT T1.'parent' [Parent], T1.'Key', T1.'Summary', T1.'Description', T1.'P', T1.'Status', T1.'Updated', CASE WHEN T1.'Updated' >= DATEADD(Day, -7, GETDATE()) THEN FORMATWIKI("{cell:Color=red}Yes{cell}") ELSE "No" End AS 'Recently Updated' FROM T1 ORDER BY T1.'key' asc

The below returns "NaN"

SELECT T1.'parent' [Parent], T1.'Key', T1.'Summary', T1.'Description', T1.'P', T1.'Status', FORMATDATE(T1.'Updated') [Updated], CASE WHEN FORMATDATE(T1.'Updated') >= FORMATDATE(DATEADD(Day, -7, GETDATE())) THEN FORMATWIKI("{cell:Color=red}Yes{cell}") ELSE "No" End AS 'Recently Updated' FROM T1 ORDER BY T1.'key' asc

Katerina Rudkovskaya _Stiltsoft_ · Answer

The date format you specify in the macro browser settings should be the same as in your source table (Jira macro). This setting is to read dates properly for further transformations.

You need to use the following date format in Settings M/dd/yy HH:mm and this line in the SQL query FORMATDATE(T1.'Updated',"M dd, yy") where you include the needed date format on your side to make this case work correctly.

Julian Governale · Answer

Thanks @Stiltsoft support for the suggestion but unfortunately it still returned a NaN.  Im using the Jira Filter macros to pull the data into the tables so there is a time part but I don't think that would make a difference since the date settings are just the date part.

Using a manual table, it renders just fine, but the Jira Macros doesn't render it correctly.   In this case, I raised a support ticket and will post the updates if resolved back to here.

Stiltsoft support · Answer

Hi @Julian Governale ,

As I see, you are talking about our Table Filter, Charts & Spreadsheets for Confluence app and its Table Transformer macro.

The dates mentioned above should be recognized well (judging by the tags, your are on Confluence Cloud and always have the latest version of the app).

Here is how it works for me (I've simplified your original query to achieve the same result):

SELECT T1.'Project', T1.'Updated',
FORMATDATE(T1.'Updated') AS 'How the macro sees your dates',
FORMATDATE("today") AS 'Today is',
CASE WHEN DATEDIFF(DAY, (T1.'Updated'), "today") <= 7
THEN FORMATWIKI("{cell:Color=red}Yes{cell}")
ELSE "No"
END
AS 'Recently Updated'
FROM T1 ORDER BY T1.'Project' ASC

The first two columns are original ones (I used a manual table for an example), the third column indicates that the macro understands "string" dates and "today" correctly.

Please try the query above for your case.

If the issue persists, please refer to our support portal for further investigation.