Can't get current month in Table Transformer

[Timur]

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

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Timur ,

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

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]

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_]

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]

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_]

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.

[Timur]

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

[Timur]

[Katerina Kovriga _Stiltsoft_]

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.

[Timur]

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_]

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.