Example of a Report without any Format

The ESQL that Spira platform uses DateTime to store date. As a result, any field storing Date data also supports storing the time value. When no formatting is applied on the date field, then, the report appears like the following. 

As you can see in the diagram above, there is also timestamp component. But, if you want the date to be formatted like "DD-MON-YYYY", then, the ESQL query needs to be customized for every date field. If multiple date fields are available, then the translations may be exhaustive and it is better to use the XSLT to be used. Please refer to an earlier KB article "Formatting Date using XSLT"  on how this can be done. 

This article assumes that you are using the ESQL to format the query to display the date in the "DD-MON-YYYY" format.

Creating the SQL Query for the Graph

1. From the administration panel, go to the "Edit Reports"
2. Add a new Report and give it a name.
3. Provide details on the header, footer, description as necessary
4. Confirm selection for category and format (ensure HTML and Excel are available as this is a tabular report)
5. Click on "Add New Custom Section" in the  Custom Sections.
6. Give a name to the custom section
7. Write the SQL Query as follows 
8. You can preview the results to see if there are any errors in the query. 
9. Click on "Create Default Template" to create the XSLT transformation required for rendering the report
10. Save the Custom Section
11. Save the Report
12. Run the Report from the Reporting area

Date formatted SQL Query

The date formatted SQL query is given below.

select 
   R.INCIDENT_ID, 
   R.CREATION_DATE,     
  (Cast(Day(R.CREATION_DATE) as string) 
    + "-" +
    CASE
     WHEN (Month(R.CREATION_DATE)) = 1 THEN "JAN"
	 WHEN (Month(R.CREATION_DATE)) = 2 THEN "FEB"
	 WHEN (Month(R.CREATION_DATE)) = 3 THEN "MAR"
	 WHEN (Month(R.CREATION_DATE)) = 4 THEN "APR"
	 WHEN (Month(R.CREATION_DATE)) = 5 THEN "MAY"
	 WHEN (Month(R.CREATION_DATE)) = 6 THEN "JUN"
	 WHEN (Month(R.CREATION_DATE)) = 7 THEN "JUL"
	 WHEN (Month(R.CREATION_DATE)) = 8 THEN "AUG"
	 WHEN (Month(R.CREATION_DATE)) = 9 THEN "SEP"
	 WHEN (Month(R.CREATION_DATE)) = 10 THEN "OCT"
	 WHEN (Month(R.CREATION_DATE)) = 11 THEN "NOV"
	 WHEN (Month(R.CREATION_DATE)) = 12 THEN "DEC"	 
	ELSE "MON"
   END  
  + "-" + 
  Cast(Year(R.CREATION_DATE) as string)) as Date 
from 
   SpiraTestEntities.R_Incidents as R 
where 
    R.PROJECT_ID=${ProjectId}

Query Explanation

1. To format the date in a specific date format like (DD-MON-YYYY) format, the built-in Day (DD), Month (MM), and Year (YYYY) functions have to be used to extract the date, month, and year part of the date. These functions return DD, MM, and YYYY as an Int32 datatype.  
2. Once the parts of the date are extracted, they will have to be converted as a string using the CAST operator. 
3. A CASE / WHEN logic is then applied for month to translate the month (DD) in numeric form to the 3-character Month (MON) format. 
4. The string components for date, month, and and year are now concatenated using the "+" operator sprinkling the field separator (-) to make them look like "DD-MON-YYYY" format.

Report Output

Here is the sample of the report output. The final column shows how the CREATION_DATE has been formatted.