TruncateTime not actually removing the time - just zeros it

Tuesday, May 11, 2021
Avatar

I'm trying to just have the YYYY-MM-DD value from a date to show in a graph and per https://www.inflectra.com/Support/KnowledgeBase/KB250.aspx i, the TruncateTime() method should do this.  But when I use it, it is still showing a time value of T00:00:00 appended.  

i.e. it simply changes a date from 2021-01-01T05:22:15 into 2021-01-01T00:00:00 .

But I want to use the date as the x axis on a graph.  This is the sample query I am using:

select DatePart, 
count(CASE
WHEN R.EXECUTION_STATUS_ID =1 THEN 1
WHEN R.EXECUTION_STATUS_ID =2 THEN -1
END
) AS Sum
from SpiraTestEntities.R_TestRuns as R 
where R.PROJECT_ID = ${ProjectId}
and DiffDays(R.START_DATE, CurrentDateTime()) < 100
group by TruncateTime(R.START_DATE) as DatePart

 I've tried converting to strings, etc to remove the T00:00:00 to no avail.

 

 

5 Replies
Tuesday, May 11, 2021
Avatar
re: lgustafson Tuesday, May 11, 2021

Screenshot here:  https://ibb.co/0yShhLb

Tuesday, May 11, 2021
Avatar
re: lgustafson Tuesday, May 11, 2021

TruncateTime() is a Microsoft built-in method that removes the time component, which it is doing.

However it does not affect the display method, so it will default to a standard date-time format, similar to how a .NET DateTime.UtcNow.Date command still returns a DateTime object with just the time zeroed out.

You need to add code to display just the date part that you want to show.

I would do that using XSLT instead. Our standard reports already do this:

<xsl:template name="format-date">
    <xsl:param name="datetime"/>
    <xsl:variable name="date" select="substring-before($datetime, 'T')" />
    <xsl:variable name="year" select="substring-before($date, '-')" />
    <xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
    <xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
    <xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
    <xsl:variable name="monthname">
      <xsl:choose>
        <xsl:when test="$month='01'">
          <xsl:value-of select="'Jan'"/>
        </xsl:when>
        <xsl:when test="$month='02'">
          <xsl:value-of select="'Feb'"/>
        </xsl:when>
        <xsl:when test="$month='03'">
          <xsl:value-of select="'Mar'"/>
        </xsl:when>
        <xsl:when test="$month='04'">
          <xsl:value-of select="'Apr'"/>
        </xsl:when>
        <xsl:when test="$month='05'">
          <xsl:value-of select="'May'"/>
        </xsl:when>
        <xsl:when test="$month='06'">
          <xsl:value-of select="'Jun'"/>
        </xsl:when>
        <xsl:when test="$month='07'">
          <xsl:value-of select="'Jul'"/>
        </xsl:when>
        <xsl:when test="$month='08'">
          <xsl:value-of select="'Aug'"/>
        </xsl:when>
        <xsl:when test="$month='09'">
          <xsl:value-of select="'Sep'"/>
        </xsl:when>
        <xsl:when test="$month='10'">
          <xsl:value-of select="'Oct'"/>
        </xsl:when>
        <xsl:when test="$month='11'">
          <xsl:value-of select="'Nov'"/>
        </xsl:when>
        <xsl:when test="$month='12'">
          <xsl:value-of select="'Dec'"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="''" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
  </xsl:template>

which you call using:

                  <xsl:call-template name="format-date">
                    <xsl:with-param name="datetime" select="UpdateDate" />
                  </xsl:call-template>

 

Wednesday, May 12, 2021
Avatar
re: inflectra.david Tuesday, May 11, 2021

I'm doing a graph not a report.  I don't see any options for adding xslt to a custom graph so I'm assuming you cannot add this to a graph... only a report?

Wednesday, May 12, 2021
Avatar
re: inflectra.david Wednesday, May 12, 2021

Thank you for this additional info.  Will try tweaking this in some other ways to get the string I want,  but could possibly change to using an aggregated date range as well as a second option.    

Statistics
  • Started: Tuesday, May 11, 2021
  • Last Reply: Wednesday, May 12, 2021
  • Replies: 5
  • Views: 185