Function FORMATDATE() allows you to fromat column CREATED in that format date as comfortable for you (see examples below).
Nomenclature:
YYYY - year in 4 number format (2022)
YY- year in 2 number format (22)
M - month (5)
MM - month in 2 number format (05)
DD - date in 2 number format (29) or (08)
hh - hours
mm - minutes
a - add AM/PM formats
LLL - Month,DD,YYYY hh:mm AM/PM
L - MM/DD/YYYY
ll - Mon Day,YYYY
EXAMPLES
YYYY-MM-DD hh:mm AM/PM
FORMATEDATE ('YYYY-MM-DD hh:mm a')
| Code Block | ||
|---|---|---|
| ||
select i.`KEY` as `Issue`, FORMATDATE(i.CREATED,'yyyy-MM-DD hh:mm a') as `Created` from ISSUE i where JQL='project = PROJECTNAME' |
Month- DD- YYYY, hh:mm AM/PM
FORMATEDATE ('LLL')
| Code Block | ||
|---|---|---|
| ||
select i.`KEY` as `Issue`, FORMATDATE(i.CREATED,'LLL') as `Created` from ISSUE i where JQL='project = PROJECTNAME' |
MM/DD/YYYY
FORMATEDATE('L')
| Code Block | ||
|---|---|---|
| ||
select i.`KEY` as `Issue`, FORMATDATE(i.CREATED,'L') as `Created` from ISSUE i where JQL='project = PROJECTNAME' |
Multiple date formats for different columns which contain Dates
| Code Block | ||
|---|---|---|
| ||
select i.`KEY` as `Issue`, FORMATDATE( i.CREATED, 'YYYY-MM-DD') as `Created`, FORMATDATE (i.DUEDATE, 'll') as `Duedate`, FORMATDATE(i.LASTVIEWED, 'MM-DD-YY hh:mm a') as `Last Viewed`, FORMATDATE(i.RESOLUTIONDATE, 'DD-MM-YYYY') as `Resolution Date`, FORMATDATE(i.UPDATED, 'LLLL a') as `Updated` from ISSUE i where JQL='project = PROJECTNAME ORDER BY created ASC' |
| Info |
|---|
PROJECTNAME* is a tentative name in our queries. Please note that in your JQLs you need to use real names of your Projects, Users, Sprints ,etc. |