SQL Query by date

Ken_Curtis

The issue is that datetime means I cannot group results by date. For example, all tickets assigned to Tier 1 by date. Each ticket tends to have its own datetime. I've played with CONVERT and other sql modifications in the query without luck. Any examples that could help?

Leigh_Kilday

Just to clarify, are you adding the FROM statement that I (oddly) left off? I also missed a ')' in my transcription...

The following works for me:

SELECT CAST(YEAR(FirstAssignedDate) as varchar(4)) + RIGHT('0' + CAST(MONTH(FirstAssignedDate) as varchar(2)), 2) as DateSort<p></p>

FROM WorkItem

Ken_Curtis

This is for dashboards mainly (I managed to save inadvertently), using the Chart with SQL tools.

Leigh_Kilday

I'd create a derived date from the datetime field for this.

E.g. Derived value for YYYYMM for sorting. You can add the day in the same way.

SELECT CAST(YEAR(FirstAssignedDate) as varchar(4) + RIGHT('0' + CAST(MONTH(FirstAssignedDate) as varchar(2)), 2) as DateSort

Ken_Curtis

This is definitely closer. So I run this in SQL with success:

SELECT TOP 25 CAST(MONTH(Created) AS varchar(2)) + CAST(DAY(Created) AS varchar(2)) + CAST(YEAR(Created) AS varchar(4))

  FROM [CiresonDB].[dbo].[WorkItem]

But from the SQL Chart Widget, this fails:
SELECT TOP 25 CAST(MONTH(wi.Created) AS varchar(2)) + CAST(DAY(wi.Created) AS varchar(2)) + CAST(YEAR(wi.Created) AS varchar(4)) AS DateSort

Note that per the example the wi.Field seems to be the required format. For example, this works:
SELECT TOP 25 wi.WorkItemId, wi.Created, wi.Title, ds.DisplayString Tier, wi.AssignedUser FROM [WorkItem] wi LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID

Leigh_Kilday

Just to clarify, are you adding the FROM statement that I (oddly) left off? I also missed a ')' in my transcription...

The following works for me:

SELECT CAST(YEAR(FirstAssignedDate) as varchar(4)) + RIGHT('0' + CAST(MONTH(FirstAssignedDate) as varchar(2)), 2) as DateSort<p></p>

FROM WorkItem

Ken_Curtis

Thanks!!