Format date and time on dashbaord

Magnus_Lundgren1

Hi

This query takes out incidents created last 7 days. i format the date to yyyy-mm-dd but in the portal is formats it as datetime, is this a bug or by design?

SELECT wi.WorkItemId ID, wi.Title Titel, ds.DisplayString Grupp, CAST(wi.Created AS date) Skapad

FROM [WorkItem] wi LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID

WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 

and wi.Created BETWEEN DATEADD(DAY, -6, GETDATE()) AND GETDATE()

and wi.TierId NOT IN ('574403f2-0585-0545-92dd-09283ac470a7','31503d03-ed20-489e-2f4b-5a6bb56d4466','26c54494-14e8-9c1f-c900-f2795825232e')

Order by Skapad

Magnus_Lundgren1

Found a solution, Replaced CAST(wi.Created AS date) Skapad with 

LEFT(CONVERT(varchar, wi.Created,126),10) Dag

Magnus_Lundgren1

Example of SQL query, get all incidents this year with both month in number and name, group by month in the SQL Chart widget

Remove wi.TierId line if you want to see everything. i choose not the display some ques like our event que

<div>SELECT wi.WorkItemId ID, wi.Title Titel, ds.DisplayString Grupp, CAST(wi.Created AS date) Skapad, DATENAME(mm, wi.Created) 'Month', DATEPART(MONTH,wi.Created) MN</div><div><br></div><div>FROM [WorkItem] wi LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID</div><div><br></div><div>WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'&nbsp;</div><div>		and DATEPART(yyyy, wi.Created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))</div><div>		and wi.TierId NOT IN ('574403f2-0585-0545-92dd-09283ac470a7','31503d03-ed20-489e-2f4b-5a6bb56d4466')</div><div>ORDER BY MN</div>