SQL Table - Return a date 30 days before another date

Question

Issue: I am using the Jira Issue/Filter macro within the Table Transformer macro in a Confluence page. I currently have the following string. See below.

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

Why displays the following table:

However, I don't want the current Build Date. I want to make my Build Date to be the Expected Impact Date minus 30 days. I think I need to do something with the function, DATEADD. However, it keeps returning an 'Invalid Date' in the cell where the new date should be.

Katerina Rudkovskaya _Stiltsoft_ · Answer

Hi @Laurie Huth,

Let's move the discussion to the support portal then.

We need to see your whole original table (all the headers and several rows visible) and an explanation of what you need to achieve with each column.

The best way to share your data is to give us your Page Storage Format (upper right corner of the page -> menu ... -> View Storage Format, if you don't see the option, ask your Confluence administrator to do it for you). Then we'll be able to recreate exactly your data and help you with the custom query.

P.S. Check the date format inside the Table Transformer macro once more as we've suggested in the first reply (Settings tab -> Date format). It should be related to the date format in your table (“d M yy” for your and my screenshots).

Laurie Huth · Answer

Thank you!

However, I'm still struggling with getting this to work. I have other SQL lines and I don't know how to incorporate this important line in. All attempts lead to a returned result of: undefined NaN in my table.

SELECT 'Key',
FORMATWIKI(SUM('Summary')) AS 'Key Summary',
FORMATWIKI(SUM('Due')) AS 'Due Date',
FORMATDATE(DATE_SUB('Due', INTERVAL 30 DAY)) as 'Date 30 days',
FORMATWIKI(SUM('Region Impact')) AS 'Regional Impact',

etc....

Natalie Paramonova _Stiltsoft_ · Answer

Hi @Laurie Huth ,

To count dates that are “minus 30 days from the existing dates in the table”, you may use the following SQL query:

SELECT T1.'Date 1',
FORMATDATE(DATE_SUB(T1.'Date 1', INTERVAL 30 DAY)) as 'Date 2'
FROM T1

The date format for the Table Transformer macro is set as “d M yy”.

Hope this helps in your case.