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,293,412
Community Members
 
Community Events
165
Community Groups

SQL Date in Table Transformer

Hi,

A simple SQL in Table Transformer, but nothing is returned.

SELECT T1.'Status', T1.'Due'
FROM T1
WHERE T1.'Resolution' = "Unresolved"
AND T1.'Due' > 2022-05-30

Macro Settings Date format is set to yy-mm-dd (see screenshot)

What am I missing? Thanks.date.png

1 answer

1 accepted

Hi @Leona ,

Try to use double quotes for your date:

Wed 15-1.png

SELECT T1.'Due', T1.'Resolution'
FROM T1
WHERE T1.'Resolution' = "Unresolved"
AND T1.'Due' > "2022-05-30"

Wed 15-2.png

Hope this helps.

Hi @Katerina Kovriga _Stiltsoft_ 

Thank you for replying. With the code below, I receive

SELECT T1.'Status', T1.'Due'
FROM T1
WHERE T1.'Resolution' = "Unresolved"
AND T1.'Due' > "2022-05-30"
ORDER BY T1.'Due' DESC

 

Something weird, for setting with yy-mm-dd, no results. All other date settings, I get result but wrong.

dates.png

The date format should correspond the format that you have in your table and in your query (see my example).

I have "yy-mm-dd" format in three places:

  • the Table Transformer macro settings
  • the "Due" column
  • the date for comparison that is "2022-05-30"

For your case, as your dates come from Jira, choose the "M dd, yy" in the Table Transformer macro and change the date for comparison as "May 30, 2022".

Then everything should work correctly.

@Katerina Kovriga _Stiltsoft_ I changed the setting to "M dd, yy" and it works!!! Thank you so much! 

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

738 views 5 21
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