SQL Order by Date Problem

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

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

SQL Query Date Sort Order Not Working

I took Ghost's suggestion and ordered it on the way out so my select code for my columns is now:

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', 
       '[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
       FROM    (SELECT DISTINCT [DATE] FROM #FileDates ) PV   order by [DATE] desc

Order by date when a format 'dd-MM-YYYY' is given in the select statement is not working as expected

If you use the same name for the column date and formatted date then the order by work for the alias column .. so is order by day, month, year as in your format ('%d-%m-%Y')

then try changing the alias name

SELECT DATE_FORMAT(Date,'%d-%m-%Y') AS My_Date
FROM Purchase
WHERE Date BETWEEN '2019-02-01' AND '2019-06-30' 
ORDER BY Date asc;

or use a proper order by format

SELECT DATE_FORMAT(Date,'%d-%m-%Y')Date
FROM Purchase
WHERE Date BETWEEN '2019-02-01' AND '2019-06-30' 
ORDER BY DATE_FORMAT(Date,'%Y-%m-%d') asc;

Order by descending date - month, day and year

I'm guessing EventDate is a char or varchar and not a date otherwise your order by clause would be fine.

You can use CONVERT to change the values to a date and sort by that

SELECT * 
FROM 
     vw_view 
ORDER BY 
   CONVERT(DateTime, EventDate,101)  DESC

The problem with that is, as Sparky points out in the comments, if EventDate has a value that can't be converted to a date the query won't execute.

This means you should either exclude the bad rows or let the bad rows go to the bottom of the results

To exclude the bad rows just add WHERE IsDate(EventDate) = 1

To let let the bad dates go to the bottom you need to use CASE

e.g.

ORDER BY 
    CASE
       WHEN IsDate(EventDate) = 1 THEN CONVERT(DateTime, EventDate,101)
       ELSE null
    END DESC

SQL order by datetime formatted as dd.MM.yyyy is incorrect

Group the datetime by converting to date and format the date after grouping:

SELECT CONVERT(VARCHAR(10), CONVERT(date, recievedDate), 104)
FROM t
GROUP BY CONVERT(date, recievedDate)
ORDER BY CONVERT(date, recievedDate)

Have an issue when ORDER BY Date DESC

Warning: The SQLite query you are about to see is ugly and should not be repeated at home, unless you totally messed up your date format, in which case you might have no other choice.

We can try doing an ORDER BY which builds out the correct two digit month and day for each date. Note that in the case of your data, this necessitates padding single digit months and days with zero.

SELECT *
FROM contacts
ORDER BY
    SUBSTR(Date, 1, 4) DESC,
    CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2
         THEN '0' || SUBSTR(Date, 6, 1)
         ELSE SUBSTR(Date, 6, 2) END DESC,
    CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1
         THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)
         ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END DESC;

Note that the correct long term solution would be for you to store all months and days as two digit numbers, padding with zero on the left in the case of single digits (your years would most likely always be 4 digits).

Here is a link to a demo which demonstrates the logic of the above query. Note that I created it using MySQL, because SQLite is not supported, but other than having to replace || with CONCAT, the query is identical.

Demo

Here is the output which shows the correct order along with the year, month, and day components correctly being extracted:

---

Related Topics