Inflectra Customer Forums: TruncateTime not actually removing the time - just zeros it (Thread)

TruncateTime not actually removing the time - just zeros it

Leslie Gustafson (leslie.gustafson@toluna.com) — Tue, 11 May 2021 20:39:02 -0400

Im 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 Ive tried converting to strings, etc to remove the T00:00:00 to no avail.

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

Leslie Gustafson (leslie.gustafson@toluna.com) — Tue, 11 May 2021 20:40:57 -0400

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

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

David J (adam.sandman+support@inflectra.com) — Tue, 11 May 2021 23:39:35 -0400

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: which you call using:

Im doing a graph not a report. I dont see any options for adding xslt to a custom graph so Im assu

Leslie Gustafson (leslie.gustafson@toluna.com) — Wed, 12 May 2021 12:59:10 -0400

Im doing a graph not a report. I dont see any options for adding xslt to a custom graph so Im assuming you cannot add this to a graph... only a report?

Sorry yes that is true. Youd need to use ESQL functions then: https://spiradoc.inflectra.com/Rep

David J (adam.sandman+support@inflectra.com) — Wed, 12 May 2021 16:27:09 -0400

Sorry yes that is true. Youd need to use ESQL functions then: https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#differences-in-literals-and-types https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#1-requirements-addedremoved-over-time https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/date-and-time-canonical-functions Day(expression) Month Year That way you can create a string format of your choosing.

Thank you for this additional info. Will try tweaking this in some other ways to get the string I

Leslie Gustafson (leslie.gustafson@toluna.com) — Wed, 12 May 2021 17:06:36 -0400

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.