Sorting Dates

[starter911]

Hi all,
I am displaying data from a database. I want the user to choose from different sorting functions and then sort the data.

All works fine except for the date:
I have the following format:

dd-mm-yyyy hh:mm:ss

If I sort it, it won't sort them correctly:
For example:

05-11-2003 13:50:15
05-12-2003 16:54:40
06-11-2003 14:58:58

I'm just using 'ORDER BY', I didn't know what else??

Also I am displaying the results with pagination. when I click the link to the second page (results 20-40), it wont show them sorted. any idea?

Thanks
A.

[YappyDog]

In order for date sorts to work, you either need to sort them as you select them out of the database, or use a yyyy-mm-dd format.

[Sxooter]

What type are you storing the dates / times as? Storing them as text won't get the proper sorting unless you're using a later model of MySQL with casting, in which cast you can case like so:

select * from table order by cast(datefield as datetime);

Otherwise, if you're on an older flavor of MySQL without casting, you'll need to make a new column with datetime type (or whatever the nonstandard name is MySQL uses, I always forget and have to look it up. 🙂) and update it to have the contents of your current text / varchar field.

[starter911]

ok, I changed the format...
seemed to be the easiest way.
regards
A.