JIRA filter on exact date

[Christian Czaia [Decadis AG]]

Hi there,

I just stumbled upon a problem which I haven't encountered before regarding filtering JIRA issues via JQL. Usually I like filtering for a specific period of time (i.e. issue due in the next 7 days etc.) Now a customer created a filter like

project = 'XYZ' and dueDate = '2012-02-20'

The problem is that no issues are being displayed even if they exist.

Am I missing something here?

Thx for your help

Christian

P.S. date values are set to default

[Mizan · Accepted solution]

Hi ,

Which version of Jira are you using ? for 4.4.4 you need to use YYYY//MM/DD format for duedate . for example duedate="2012/03/01" . dont forget the quotes :)

[Andrew Frayling]

Hi Christian,

Single quotes, double quotes, /'s, -'s should all work (tested on local JIRA 5 instance). Stab in the dark, could it be an indexing problem? Is it possible to re-index and try again?

Andrew.

[Christian Czaia [Decadis AG]]

Hi, I'm usig JIRA 5.0 but we've tested the query in older version as well with no results...Single quotes, double quotes. I'll keep trying and keep u informed

[Christian Czaia [Decadis AG]]

I have tried re-indexig with no results. hat I wanted (as an example) are the issues created on Feb 27.

No results for this query:

createdDate = "2012-02-27"

All the relevant issues after this query:

createdDate >= "2012-02-27" and createdDate < "2012-02-28"

[Jobin Kuruvilla [Adaptavist]]

And we thought you were asking bout dueDate! dueDate and createdDate are different.

dueDate only stores the date and hence you can use = in the JQL to search issues due on a date. createdDate stores time as well and hence = will work only if you include the exact time. You need to use > <= symbols as you have done above to get issues created on a date.

[Christian Czaia [Decadis AG]]

Thank you guys. In SQL I would need something like a datepart to get the date. So now I know that "createdDate" is a timestamp... I thought of that before and tried to test the query against dueDate which didn't produce any results either. THAT must have been an indexing problem and I thought it was a general probem I was facing!

cheers

Christian

[Igor Rodrigues]

To restrict your search by a specific date you need to follow this:

DD = The actual date you want to have results for

DD+1 = The actual date you want + 1

Obviously, if DD is 31, for instance, DD+1 will be 1 and you'll need to update the MM to MM+1, same for YYYY - i.e.: Date you want: 2014-12-31; 2nd date will be: 2015-01-01

createdDate >= "YYYY-MM-DD" and createdDate < "YYYY-MM-DD+1"

[Kundan Mukherjee]

The below worked for me in JIRA 8.8.0

....AND dueDate >= 2020-3-1 AND dueDate <= 2020-3-31

[Ahmed Gehad]

You can send the value for field 'createdDate' in formats such as: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

[Filipe]

Hi Cristian,

Can i suggest :

https://marketplace.atlassian.com/plugins/pt.lt.lfcribeiro.jira.jqlLTFunctions

Thers a function "ExecuteQuery()" that lets you run a SQL statement.

Regards,