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
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.