CODEWITHSUNDEEP
mysqlsqldatedate-format
user3895205
user3895205

Reputation: 5

MySQL DATE_FORMAT not outputting dates in correct order

I am trying to order a table of dates and times by date ascending, then start time ascending, however my list of dates is not displaying in the correct order. The list of start times does seem to work though.

The order should be displayed as all entries for 18/08/2014 then 19/08/2014 then 20/08/2014 then .... 01/09/2014 then 02/09/2014 ..., etc, with 16/09/2014 being the last entry.

Note: I have stored the date and start times each as a VARCHAR in my MySQL table and don't/can't change this now. Every date follows the dd/mm/yyyy (UK English) format.

Can anyone please help me by pointing out what I've missed?

Table setup for table 'schedule'

date         VARCHAR(255)
start_time   VARCHAR(255)

MySQL query

SELECT * FROM schedule WHERE username='".$_SESSION['username']."' ORDER BY DATE_FORMAT(date, '%d/%m/%Y') ASC, start_time ASC

Table output

Date        Start time  End time
16/09/2014      
18/08/2014  10:00       16:00
21/08/2014  10:00       12:00
28/08/2014  10:00       12:00
10/09/2014  10:00       12:00
11/09/2014  10:00       12:00
12/09/2014  10:00       12:00
21/08/2014  13:00       15:00
28/08/2014  13:00       15:00
10/09/2014  13:00       15:00
11/09/2014  13:00       15:00
12/09/2014  13:00       15:00
01/09/2014  15:00       15:30
28/08/2014  15:00       15:30
21/08/2014  15:00       15:30
02/09/2014  15:00       15:30
03/09/2014  15:00       15:30
04/09/2014  15:00       15:30
05/09/2014  15:00       15:30
08/09/2014  15:00       15:30
09/09/2014  15:00       15:30
10/09/2014  15:00       15:30
11/09/2014  15:00       15:30
12/09/2014  15:00       15:30
15/09/2014  15:00       15:30
16/09/2014  15:00       15:30
21/08/2014  16:00   
10/09/2014  16:00   
11/09/2014  16:00

Any help much appreciated!

Upvotes: 0

Views: 244

Answers (2)

Arth
Arth

Reputation: 13110

First of all, I'd revisit the idea of storing any dates or times as strings.. it leads to exactly this kind of problem.

MySQL actually treats dates as strings anyway, with added date functionality.. and stores them in the more sensible format of %Y-%m-%d, which produces the correct ordering.

If you absolutely cannot change the database.. either contact someone who can or run your query as:

  SELECT * 
    FROM schedule 
   WHERE username='".$_SESSION['username']."' 
ORDER BY STR_TO_DATE(date, '%d/%m/%Y') ASC, start_time ASC

DATE_FORMAT casts a date to the string format specified. In your case it was implicitly converting your string into a date (as it was passed into the function) and then formatting it back to it's original format.. hence the ordering you are seeing.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The function date_format() formats a date and outputs a string. You want str_to_date():

ORDER BY STR_TO_DATE(date, '%d/%m/%Y')

Upvotes: 1

Related Questions