CODEWITHSUNDEEP
sqlsql-servert-sql
Atharv Joshi
Atharv Joshi

Reputation: 53

In SQL Server, order by date not showing all dates in desc order

I have to show daily collected amount in admin panel of one e-commerce portal in descending order of date. But when I am trying to showing it will showing daily collected amount but showing randomly. 

Dates       Amount
------------------
30/12/2017  36010   
30/11/2017  42780   
29/12/2017  23090   
29/11/2017  66060   
28/12/2017   4700   
28/11/2017  84370

But I have to show this in below order 

Dates       Amount
--------------------
30/12/2017  36010
29/12/2017  23090
28/12/2017   4700       
30/11/2017  42780   
29/11/2017  66060   
28/11/2017  84370

This is my sample code , please correct me, where I am wrong

SELECT
    (SUM(MonthAmount) + SUM(LateFine)) AS DailyCollection, 
    CONVERT(VARCHAR(11), ApprovedDate, 103) AS InboxDate  
FROM
    [dbo].[SlipDetails] 
WHERE
    ApprovedByAdmin = 'A' 
GROUP BY 
    CONVERT(VARCHAR(11), ApprovedDate, 103) 
ORDER BY 
    InboxDate DESC

Upvotes: 2

Views: 4841

Answers (5)

abdulla wasay
abdulla wasay

Reputation: 47

It seems that ordering is being performed based on varchar datatype. You Need to order records based on date data type.

Upvotes: 0

Atharv Joshi
Atharv Joshi

Reputation: 53

select (sum(MonthAmount) + sum(LateFine)) as DailyCollection, 
CONVERT(varchar(11),ApprovedDate,103) as InboxDate  
from [dbo].[SlipDetails] where ApprovedByAdmin='A' 
group by CONVERT(varchar(11),ApprovedDate,103) 
order by CONVERT(DATE,  CONVERT(varchar(11),ApprovedDate,103),103) desc

Upvotes: -1

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Do it like below :

select DailyCollection, InboxDate from (
    select (sum(MonthAmount) + sum(LateFine)) as DailyCollection, 
    CONVERT(varchar(11),ApprovedDate,103) as InboxDate  
    from [dbo].[SlipDetails] where ApprovedByAdmin='A' 
    group by CONVERT(varchar(11),ApprovedDate,103)
) as T order by convert(datetime, InboxDate, 103) desc

As you are converting your datetime column to varchar, you will not get the desired result if you order with the converted varchar column, in that case, you can use derived table and converted back the varchar column to datetime while doing the order like i did above.

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Use ....Order by ApprovedDate desc

You are sorting a string/text value not a date value (because you are converting the ApprovedDate to string before the sorting)

If you want the date field in that specified format you can use the below method. ie you can convert that formatted date string to Date datatype for sorting only.

select (sum(MonthAmount) + sum(LateFine)) as DailyCollection, 
CONVERT(varchar(11),ApprovedDate,103) as InboxDate  
from [dbo].[SlipDetails] where ApprovedByAdmin='A' 
group by CONVERT(varchar(11),ApprovedDate,103) 
order by CONVERT(DATE,  CONVERT(varchar(11),ApprovedDate,103),103) desc

Note :- As mentioned by @Larnu, better keep your data types as they are, and change display formats in your presentation layer.

Upvotes: 3

Thom A
Thom A

Reputation: 95544

The problem here is you are converting your date to a varchar and then sorting on it. The order of a varchar is VERY different to that of a date. For example, in date terms, 16/01/2018 is before 01/12/2020. in varchar terms, however, 01/12/2020 is before 16/01/2018. Why? Because 0 is before 1.

Keep your data types as they are, and worry about display formats in your presentation layer.

Upvotes: 0

Related Questions