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,300,003
Community Members
 
Community Events
165
Community Groups

Can't get current month in Table Transformer

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

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.

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?

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.

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

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

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

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

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

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
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

336 views 2 7
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