Can't get current month in Table Transformer

Timur June 17, 2022

Hi

I'm trying to show via Table Transform macros only rows related with current month:

SELECT * FROM T*
WHERE MONTH('End date') = MONTH(CURRENT_TIMESTAMP)

And get the error like: table have no rows

When I do it like
SELECT * FROM T*
WHERE MONTH('End date') = 6

All works fine.

Could you please assist me to resolve this

1 answer

1 accepted

3 votes
Answer accepted
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 19, 2022

Hi @Timur ,

I couldn't replicate the issue on our test Server instance:

Sun 1-1.pngSun 1-2.pngSun 1-3.png

Please check which version of the app you are using - the current one is 8.7.1. Please update and check the query once more.

Timur June 19, 2022

Hi Katerina. Thank you fir your answer. I'm using 7.13.7 version.
This is instance of my organization. Do you think that the root of issue is in instance version?

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 20, 2022

It certainly may be so: there were at least 3 releases regarding the Table Transformer macro and its functions since your version.

You may also check what the MONTH(CURRENT_TIMESTAMP) function returns (show it as a separate column). For me it works fine and returns 6 for every cell. So the comparison also works properly.

And you may check the date format that you use inside the Table Transformer settings (I guess that it corresponds the date format in your table, because the second query is working well).

So update the app, and if the issue is still present, raise a support request. Attach the page storage format (upper right corner -> menu ... -> View Storage Format). If you don't see the option, ask your Confluence administrator to help you.

Our support team will restore exactly your source table and your macro and look into the issue. If your source table is the Jira Issues macro, add its screenshot in a published state without any macros.

Timur June 20, 2022

Thanks for your prompt. And could you please show how you return to separate column function?
I mean the command. Just to delete misunderstanding and further issues if I do it different way

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 20, 2022

SELECT *,

MONTH(CURRENT_TIMESTAMP) AS 'Month number'

FROM T*

This query will put the month number extracted from the current date in a separate column named 'Month number'.

For me it goes as expected, and I get "6" in every cell.

Like Timur likes this
Timur June 20, 2022

xmm, interesting. For me it create new column, but every cell is empty
let me try with new table

Timur June 21, 2022

Screenshot 2022-06-21 100521.png

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 21, 2022

It means that the function is not working (and so is the initial query - it doesn't get the number for your WHERE condition) - update the app and check the results.

If nothing helps, raise a support ticket like I've suggested above.

Like Timur likes this
Timur June 23, 2022

Hi Katerina,

Could you please show which date format you have used in your first answer?
Now function is working (checked on putting month number to separate table). But I still can't reach the result.
I have used all data variants described here https://docs.stiltsoft.com/display/public/TFAC/How+to+set+the+date+format+and+worklog+settings
it works with string dates but not working with Date macro

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 23, 2022

It was the dd M yy  format for me that corresponds to the 16 Jun 2021, for example. Your format seems to be the same.

This format should be present in the suggestions for the Table Transformer macro settings.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events