Created By  inflectra.adam Wednesday, April 5, 2017

Sometimes you want to create a new custom report with a list of fields from SpiraTest that includes the date that a test cases was executed or the date that a defect was logged, but you don't want to clutter the report with the time part. Alternatively you want to join two tables on a date-time field where only a date comparison is needed.

Solution: Entity SQL Date Functions

When writing the custom query, for example, you can display a list of incidents and their creation date/times as follows:

select R.INCIDENT_ID, R.CREATION_DATE from SpiraTestEntities.R_Incidents as R where R.PROJECT_ID = ${ProjectId}

This displays the following:

INCIDENT_IDCREATION_DATE
12003-11-01T00:00:00
22003-11-01T00:00:00
32003-11-01T00:00:00
42003-11-02T00:00:00
52003-11-02T00:00:00
62003-11-02T00:00:00
72003-11-04T00:00:00
82003-11-04T00:00:00
92003-11-04T00:00:00
102003-11-04T00:00:00

 

To get just the date-values, you need to use the TruncateTime() function:

select R.INCIDENT_ID, TruncateTime(R.CREATION_DATE) as CREATION_DATE from SpiraTestEntities.R_Incidents as R where R.PROJECT_ID = ${ProjectId}

For a full list of date-related functions you can use in EntitySQL, please refer to:

https://msdn.microsoft.com/en-us/library/bb738563(v=vs.110).aspx

Article Info
  • Last Updated: 4/5/2017
  • Article ID: KB250
  • Views: 551