Format DATE

Function FORMATDATE() allows you to modificate format column CREATED in that format date as comfortable for you.

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')

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')

select i.`KEY` as `Issue`,
FORMATDATE(i.CREATED,'LLL') as `Created`
from ISSUE i
where JQL='project = PROJECTNAME'

MM/DD/YYYY

FORMATEDATE('L')

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

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'

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.