MySQL Date formatting NOT sorting in correct order

its_macman

Hi,

I use the "date_format(date_completed, '%e/%c/%y') ORDER by date_completed asc" to retrieve date from a MySQL 'date' field. This works fine.

The problem is, the wrong sort order is displayed, for example 10/10/02 comes before 09/07/02. I know it is because of the leading zero in 09/07/02 but I can not find how to tell PHP to ignore the zero.

NOTE: The same search works as I would expect using MySQL

I have looked everywhere for the answer.

Regards

hand

I'm a little bit confused. You have two dates stored in a mysql database in a field with data type "date".

These dates - 7th Sept. and 10th Oct. - are stored with format "YYYY-MM-DD" (Year-Month-Day)
"2002-09-07"
"2002-10-10"

Now you are selecting these dates using the format strings:
%e Day of the month, numeric (0..31), 1 or 2 digits
%c Month, numeric (1..12), 1 or 2 digits
%y Year, numeric, 2 digits

7th Sept. -> '%e/%c/%y' -> "9/7/02"
10th Oct. -> '%e/%c/%y' -> "10/10/02"

You are sorting them by date_completed ascending (and not by the formatted character string).

I can't believe that the result shows
"10/10/02" in front of
"9/7/02"
(this is descending order)

Why you're example shows "09/07/02" instead of "9/7/02". Does your samples show day/month/year or month/day/year format?

Or would you like to sort the formatted Date like ... date_format(date_completed, '%e/%c/%y') as FORM_DATE ORDER by FORM_DATE ...
?

its_macman

Thank you for your help. I hope this makes it clearer.
mysql> select date_completed from pre order by date_completed desc;
+----------------+
| date_completed |
+----------------+
| 2002-11-13 |
| 2002-11-12 |
| 2002-11-12 |
| 2002-11-08 |
| 2002-11-06 |
| 2002-11-05 |
| 2002-11-05 |
| 2002-11-05 |
| 2002-10-30 |
| 2002-10-24 |
| 2002-10-24 |
| 2002-10-23 |
| 2002-10-22 |
| 2002-10-16 |
| 2002-10-15 |
| 2002-10-14 |
| 2002-10-09 |
| 2002-09-24 |

PHP Syntax:

$sql ="SELECT id, fname, lname, dept, work_done, consultant, date_format(date_completed, '%e/%c/%y') as date_completed, job_time_hr FROM $table_name WHERE dept LIKE '%'\"$param3\"'%' order by date_completed desc";

HTML Result:
8/11/02
6/11/02
5/11/02
5/11/02
5/11/02
30/10/02
24/9/02
24/10/02
24/10/02
23/10/02
22/10/02
16/10/02
15/10/02
14/10/02
13/11/02
12/11/02
12/11/02

Regards

Weedpacket

Well yes, you're formatting the date as a string, and then ordering by the formatted string - "date_format(date_completed, '%e/%c/%y') as date_completed .... order by date_completed desc".

Give the formatted date a different name from the field so that it doesn't stomp on the original.

its_macman

Excuse my ignorance.

.... "date_format(date_completed, '%e/%c/%y') as completed .... order by date_completed desc".

Now this leave the HTML result (The column named, completed) blank

Regards

hand

Now you should print (echo) instead of the content of the field "date_completed" the content of the field "completed".

may be $row[completed] instead of $row[date_completed] it depnds on your script