nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Compare a timestamp and format 'YYYY/MM/DD', 'YYYY-MM-DD'

Adrian Speich_81tsmd

I am attempting to compare two dates in JQL and realised I am comparing a date from the calendar object and a timestamp, resulting in the below error:

Date value 'Started: Approval[Time stamp]' for field 'Deployment SLA' is invalid. Valid formats include: 'YYYY/MM/DD', 'YYYY-MM-DD', or a period format e.g. '-5d', '4w 2d'.

Is it possible to convert a timestamp to format 'YYYY/MM/DD' or 'YYYY-MM-DD'?

Accepted answers

Trudy P Claspill

Hello @Adrian Speich

Welcome to the Atlassian community.

Can you share with us the actual JQL you are attempting to use?

I suspect you are trying something like this:

'Deployment SLA' < 'Started: Approval[Time stamp]'

Comparing one date (or date/time) field directly to another in a JQL statement is not supported by native Jira JQL capabilities. To achieve that would require a third party app, such as ScriptRunner which provides a dateCompare() function.

(I do not work for Adaptavist and get no perks from recommending their product. It is just the one with which I am most familiar.)

There are other apps that extend JQL capabilities available in the Atlassian Marketplace , and one or more of them may also provide a comparable function.

All comments

Kristján Geir Mathiesen

Hi @Adrian Speich and welcome to the party!

Have you tried: When querying in JQL, use the [field] ~ "jiraDate" format to extract only the date from a date/time field.

Like --> JQL query to find issues created on "2023-12-25": created ~ "jiraDate" AND createdDate = "2023-12-25"

HTH,
KGM

Trudy P Claspill

Hello @Adrian Speich

Welcome to the Atlassian community.

Can you share with us the actual JQL you are attempting to use?

I suspect you are trying something like this:

'Deployment SLA' < 'Started: Approval[Time stamp]'

(I do not work for Adaptavist and get no perks from recommending their product. It is just the one with which I am most familiar.)

There are other apps that extend JQL capabilities available in the Atlassian Marketplace , and one or more of them may also provide a comparable function.

Adrian Speich_81tsmd

Hi Trudy,

That is unfortunate, I will need to make a request my company's admin to install ScriptRunner and this is unlikely to be approved.

Yes, your suspicion is 100% correct regarding the JQL line in question, that is exactly what I am trying.

Thanks

Adrian

Adrian Speich_81tsmd

Hi Kristján,

I have not tried that. How would it look if I wanted to extract the date from a timestamp in the example below?

'Deployment SLA' < 'Started: Approval[Time stamp]'

Deployment SLA format = DD/MM/YYYY

Started: Approval[Time stamp] format = DD/MM/YYYY HH:mm

Thanks.

Trudy P Claspill

Hello @Adrian Speich

What do you need to do with the output of the filter? What is the actually problem you are trying to solve with this filter?

Depending on what problem you are trying to solve, there may be work arounds available that don't involve buying an app.

Adrian Speich_81tsmd

The output of the filter will be used to feed a dashboard and/or exported to Excel.

I am looking to highlight issues that have exceeded their SLA date. When the ticket transitions to a particular status, 'Started: Approval [Timestamp]' is populated with now().

I want to identify any issues where 'Started: Approval [Timestamp]' exceeded the SLA ('Deployment SLA').

I export the raw data into a spreadsheet and perform the calculation via a macro at the moment.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-cloud