Help with ORDER BY..

[M-Jay]

Hi!

I need some help with the command ORDER BY..

I want to order a field containing a date (dd.mm.yy).
The field has the type: VARCHAR..

I have tried to use: ORDER BY date ASC
and: ORDER BY date DESC

But when it orders, it orders by the days..

I'm not quite sure how to describe it in english, because my english isn't that good, but I can give you an example:

02.03.03
test3

05.01.03
test 5

12.01.03
test2

12.12.03
test4

That is how it orders now, but I really want it like this:

05.01.03
test 5

12.01.03
test2

02.03.03
test3

12.12.03
test4

I know that I could use this format: mm.dd.yy, but that is not the order we use in my country...
Is there another way?? Please help me... And if you don't quite understand what I'm trying to say, then tell me what you don't understand and I will try to explain in another way...

[hand]

Is there a special reason why you didnt take DATE or DATETIME as field format? If possible please change it. If you can do so its very easy and you'll get a lot of other functionalities
http://www.mysql.com/doc/en/Date_and_time_functions.html

If it is not possible take a look at this thread.
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10209421

[M-Jay]

Hi there...

This is taken from the link you gave me:

"Although MySQL tries to interpret values in several formats, it always expects the year part of date values to be leftmost. Dates must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98')."

I do not want to have the year infront..

I know that I could use this format: mm.dd.yy, but that is not the order we use in my country...

But if that is the only way, I guess I have to do soo... 😕

[hand]

You can retrieve every dateformat you need:

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'

or

mysql> SELECT DATE_FORMAT('1999-01-31', '%d.%m.%y');
        -> '31.01.99'

[hand]

To pupulate the date fiield with the actual timestamp

UPDATE TABLE SET dateintable = now();

That's all

An selecting it

SELECT DATE_FORMAT(dateintable, "%d.%m.%Y") AS dateintable FROM TABLE

Today you will get:

03.01.2003

[nnichols]

You made a major blunder in your database design. This is quite a common question with an incredibly simple answer -

MySQL's date formats all use yymmdd for the simple reason that -

030102 comes before
030103 and it allows for simple arithmetic sorting algorithms.

You can see clearly that this does not work using dd.mm.yy. But if you store your data in the yymmdd format you can easily SELECT it using MySQL's DATE_FORMAT which will allow you to return it in any form you wish -

SELECT DATE_FORMAT(date_col, "%d.%m.%y") AS date

- this returns "dd.mm.yy" although it is stored in "yymmdd"

Hope this helps 😉

[M-Jay]

SELECT DATE_FORMAT(date_col, "%d.%m.%y") AS date

• this returns "dd.mm.yy" although it is stored in "yymmdd"

Does this mean that it displays dd.mm.yy, but it is stored in yy.mm.dd and is also sorted yy.mm.dd??

[nnichols]

yes

[M-Jay]

Ok... I will try to figure it out...

Thanks to all of you!!! 😃 😃

[hand]

You're welcome

[M-Jay]

I didn't get it to work... Don't know if I understood it right.. This is the code I tried:

@mysql_query("SELECT DATE_FORMAT(DATE, "%d.%m.%Y") AS DATE, INFO FROM news ORDER BY DATE DESC");

But I got an Parse Error... 🙁 🙁

What am I doing wrong??

[nnichols]

@mysql_query("SELECT DATE_FORMAT(DATE, '%d.%m.%Y') AS DATE, INFO FROM news ORDER BY DATE DESC");

Remember to escape "s inside "s or use 'single quotes'.

[M-Jay]

Ok, but it still isn't ordering in the correct order... it returned:

14.05.2003
test4

12.12.2003
test2

10.12.2003
test3

02.01.2003
test 1

I'm confused.... 😕

[nnichols]

@mysql_query("SELECT DATE_FORMAT(DATE, '%d.%m.%Y') AS NEW_DATE, INFO FROM news ORDER BY DATE DESC");

You are sorting by DATE which is what you aliased the DATE_FORMAT() to.

[M-Jay]

😃 Alright!

Now it works!! Thanx ALOT! You have been great..

I love this forum... 😃

I learned something today too... 🆒

[nnichols]

Glad you got it working ok now 😉