Sorting Dates and Serial Number

advertisements
Date                        SrNo
SEL/2016APR01/002/000001    01
SEL/2016APR02/002/000001    04
SEL/2016APR03/002/000001    03
SEL/2016JAN01/002/000001    02

I need to sort the first column Date part(2016JAN01) in Descending order and if there are two same dates then I need to sort the second column in Descending order.

substr('SEL/2016APR01/002/000001', 4,12) gives me 2016APR01 but I dont know how to sort dates of that format.

My query looks something like this

SELECT *
FROM tablename
ORDER BY substr(Date) DES, SrNo DESC


Update:

This first answer covers SQL Server, while the answer belows it is for MySQL. You originally tagged your question MySQL, and so everyone answered for that.

For SQL Server you can use CONVERT:

ORDER BY CONVERT(DATETIME,
                 SUBSTRING(Date, 12, 2) + ' ' +
                 SUBSTRING(Date, 9, 3) + ' ' +
                 SUBSTRING(Date, 5, 4),
                 106) DESC,
         SrNo DESC

If you were actually using MySQL, then STR_TO_DATE would be the way to go:

ORDER BY STR_TO_DATE(SUBSTR(Date, 4, 12), '%Y%b%d') DESC,
         SrNo DESC

Here is what the formatting parameters mean:

%Y - four digit year
%b - three letter month (e.g. JAN, APR)
%d - two digit day of month

If the upper case format of the month names results in STR_TO_DATE not working, you can try the following (which is admittedly a bit ugly):

ORDER BY STR_TO_DATE(
             CONCAT(SUBSTRING(Date, 5, 4),
                    SUBSTRING(Date, 9, 1),
                    LOWER(SUBSTRING(Date, 10, 2)),
                    SUBSTRING(Date, 12, 2)),
             '%Y%b%d') DESC,
         SrNo DESC

As @RiggsFolly mentioned, your life would made easier if you stored your dates in a standard MySQL format.