Dates not ordering correctly 2

[Cpreston]

Hi

I have the following code but it is not ordering the dates correctly.

SELECT TOP (100) PERCENT CONVERT(VarChar(10), DateRequired, 103) AS [Date Required], SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE))) AND (OrderStatus <> 1) AND (OrderStatus <> 0) AND
(OrderStatus <> 11)
GROUP BY CONVERT(VarChar(10), DateRequired, 103)
ORDER BY [Date Required] DESC

It groups the dates like this

29/05/2015
28/05/2015
27/05/2015
03/06/2015
02/06/2015
01/06/2015

Where it should be

27/05/2015
28/05/2015
29/05/2015
01/06/2015
02/06/2015
03/06/2015

I tried changing the last line to

ORDER BY CONVERT(VarChar(10), DateRequired, 103) DESC

But I got the same result, I have tried many variations but cannot get the result I need. Any ideas how I can change the code so it order's correctly

Thanks

 

[fredericofonseca]

order by DateRequired desc
or
order by convert(varchar(10), DateRequired, 112) desc

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi
Thanks for the reply

With

ORDER BY DateRequired desc I am getting
Column "dbo.148-vwOrderHeadCP.DateRequired" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

And with

ORDER BY convert(varchar(10), DateRequired, 112) desc I am getting
Msg 8127, Level 16, State 1, Line 6
Column "dbo.148-vwOrderHeadCP.DateRequired" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Any ideas please

 

[fredericofonseca]

select *
from (
SELECT TOP (100) PERCENT CONVERT(VarChar(10), DateRequired, 103) AS [Date Required], SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE))) AND (OrderStatus <> 1) AND (OrderStatus <> 0) AND 
(OrderStatus <> 11)
GROUP BY CONVERT(VarChar(10), DateRequired, 103)
) t
ORDER BY convert(date,[Date Required],103) DESC

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

Thanks fredericofonseca that appears to work good for me.

What is the t for by ORDER BY convert please

Thanks

 

[Olaf Doschke]

The t is an alias name for the subquery. This is an indirect CTE.

I wonder, if it isn't much simpler to select, group by and order by the original field, as in:

SELECT TOP (100) PERCENT DateRequired, SUM(TotalVolume) AS M3
FROM dbo.[148-vwOrderHeadCP]
WHERE (DateRequired >= CAST(GETDATE() AS DATE)) AND (DateRequired <= DATEADD(day, 7, CAST(GETDATE() AS DATE))) AND (OrderStatus <> 1) AND (OrderStatus <> 0) AND
(OrderStatus <> 11)
GROUP BY DateRequired
ORDER BY DateRequired ASC

This obviously requires DateRequired to be a date field (no pun intended), not a datetime field, but this seems to be the case, your where clause casting AS DATE suggests this.
Do the conversion of date to whatever suits you for display at the frontend or in the report, not in your query.

Bye, Olaf.

 

[Cpreston]

Hi

Thanks Olaf. I ran your query and it returns dateformat and in yyy-mm-dd. I needed it dd/mm/yyyy and no time.

Don't still quite understand the t side of things (fairly new to programming) and how it fits in with the query.

Thanks for the replys

 

[Olaf Doschke]

The format is merely depending on whatever you set for date display at client side of things, it doesn't matter what SQL Server Management studio displays. This is a date field, the human readable value shown depends on the client, not on the server.

It'll come over correctly in your web application or report or whatever, if you simply do the right settings, eg in C# it's your culture and locale.

Don't convert to a string where you want a date indeed. Dates sort wrong as strings. The solution you accept is overly complicated, does convert and reconvert just to be able to order by correctly and has some specialties you don't even understand, when they are explained in short to you and I don't have the time for a lengthy explanation.

Take it or leave it.

Bye, Olaf.

 

[Cpreston]

Hi Olaf

yes quite correct, I Implemented your query into the dashboard and it indeed changed date format to dd/mm/yyyy the system default.

Many thanks