Select date as dmy and sort correctly?

Discussion in 'MySQL' started by Sn@Ke, May 12, 2011.

0
  1. #1
    I have a query that looks like this:

    
$latest = mysql_query("SELECT id, DATE_FORMAT(`date`, '%d-%m-%Y') AS date, name, suburb, source, rating, salesperson 
FROM lead 
ORDER BY date DESC LIMIT 10;") or die(mysql_error());
    Code (markup):
    Which works perfect, BUT it's not sorting the results by date correctly. It's only sorting by DAY.

    This wouldn't be a problem if I just sort by the mysql datefield.. but I live in Australia and we use date,month,year not the standard year,day,month..
     
    Sn@Ke, May 12, 2011 IP
  2. Sn@Ke

    Sn@Ke Well-Known Member

    Messages:
    334
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    155
    #2
    after a heap of googling... it turns out you can only sort by largest to smallest..
    So I had to work around it by doing this:

    $latest = mysql_query("SELECT id, date AS datesort, DATE_FORMAT(`date`, '%d-%m-%Y') AS date, name, suburb, source, rating, salesperson FROM lead ORDER BY datesort DESC LIMIT 10;") or die(mysql_error());


    Kind sucks.. but works.

    Maybe someone can tell me a better way without storing day, month, year in separate columns.

    meh
     
    Sn@Ke, May 12, 2011 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    You should be able to format the date column separately and still sort by the original values. Is the column an actual date column or are the dates being stored in a char or varchar?
     
    jestep, May 13, 2011 IP