Scott, I am glad that you understand what I meant but I still don't think you understand what you can do with MySQL. You are killing your query performance by trying to convert things on the fly..... YOU DON'T NEED TO DO THAT WITH MYSQL. Maybe you did in DB2 but not here.
So you can stop Googling for the documents, here is a link to the MySQL manual that heads up the section on almost all of the functions in MySQL: (http://dev.mysql.com/doc/mysql/en/Functions.html). May I suggest you look at the Date_Format() function as an easier method to re-format your dates for display. Here's an example of what I mean. Imagine I have a table of appointments that looks like: CREATE TABLE appointment ( ID int not null auto_increment, OwnerName varchar(20) not null, MeetWithName varchar(20) not null, Date datetime, PRIMARY KEY(ID) ) (YES, this is _not_ a normalized table design but it doesn't need to be for this example) and I have populated it with the following data: INSERT appointment (OwnerName, MeetWithName, Date) VALUES ('Shawn','Dr. Bell', '2004-08-05 14:00:00') ,('Shawn', 'Dr. Bell','2004-08-12 11:00:00') ,('John','Mary','2004-07-31 12:15:00') , ('John','Mary','2004-08-04 12:15:00') , ('John','Mary','2004-08-11 11:15:00') , ('John','Mary','2004-08-25 12:15:00') , ('Scott', 'David','2004-08-01 09:00:00') , ('Scott', 'James', '2004-08-07 15:00:00') , ('Shawn', 'Scott', '2004-08-06') , ('Scott', 'Anna','2004-08-22'); Here is the data as it sits in the table: select * from appointment; +----+-----------+--------------+---------------------+ | ID | OwnerName | MeetWithName | Date | +----+-----------+--------------+---------------------+ | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | | 3 | John | Mary | 2004-07-31 12:15:00 | | 4 | John | Mary | 2004-08-04 12:15:00 | | 5 | John | Mary | 2004-08-11 11:15:00 | | 6 | John | Mary | 2004-08-25 12:15:00 | | 7 | Scott | David | 2004-08-01 09:00:00 | | 8 | Scott | James | 2004-08-07 15:00:00 | | 9 | Shawn | Scott | 2004-08-06 00:00:00 | | 10 | Scott | Anna | 2004-08-22 00:00:00 | +----+-----------+--------------+---------------------+ 10 rows in set (0.00 sec) Now, To sort this data different ways. Sorted by OwnerName: SELECT * from appointment order by OwnerName; +----+-----------+--------------+---------------------+ | ID | OwnerName | MeetWithName | Date | +----+-----------+--------------+---------------------+ | 6 | John | Mary | 2004-08-25 12:15:00 | | 3 | John | Mary | 2004-07-31 12:15:00 | | 4 | John | Mary | 2004-08-04 12:15:00 | | 5 | John | Mary | 2004-08-11 11:15:00 | | 8 | Scott | James | 2004-08-07 15:00:00 | | 7 | Scott | David | 2004-08-01 09:00:00 | | 10 | Scott | Anna | 2004-08-22 00:00:00 | | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | | 9 | Shawn | Scott | 2004-08-06 00:00:00 | | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | +----+-----------+--------------+---------------------+ 10 rows in set (0.00 sec) Sorted by OwnerName and Date: SELECT * from appointment order by OwnerName, Date; +----+-----------+--------------+---------------------+ | ID | OwnerName | MeetWithName | Date | +----+-----------+--------------+---------------------+ | 3 | John | Mary | 2004-07-31 12:15:00 | | 4 | John | Mary | 2004-08-04 12:15:00 | | 5 | John | Mary | 2004-08-11 11:15:00 | | 6 | John | Mary | 2004-08-25 12:15:00 | | 7 | Scott | David | 2004-08-01 09:00:00 | | 8 | Scott | James | 2004-08-07 15:00:00 | | 10 | Scott | Anna | 2004-08-22 00:00:00 | | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | | 9 | Shawn | Scott | 2004-08-06 00:00:00 | | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | +----+-----------+--------------+---------------------+ 10 rows in set (0.00 sec) Notice that now for each name, their appointments are in the correct date order? Sorted by OwnerName, MeetWithName, then Date: SELECT * from appointment order by OwnerName,MeetWithName,Date; +----+-----------+--------------+---------------------+ | ID | OwnerName | MeetWithName | Date | +----+-----------+--------------+---------------------+ | 3 | John | Mary | 2004-07-31 12:15:00 | | 4 | John | Mary | 2004-08-04 12:15:00 | | 5 | John | Mary | 2004-08-11 11:15:00 | | 6 | John | Mary | 2004-08-25 12:15:00 | | 10 | Scott | Anna | 2004-08-22 00:00:00 | | 7 | Scott | David | 2004-08-01 09:00:00 | | 8 | Scott | James | 2004-08-07 15:00:00 | | 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 | | 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 | | 9 | Shawn | Scott | 2004-08-06 00:00:00 | +----+-----------+--------------+---------------------+ 10 rows in set (0.00 sec) To list the appointments with the most recent first) and reformat the dates to be in "mm/dd/yy hh:nn:ss A/PM" format: SELECT DATE_FORMAT(date, '%m/%d/%y %r') as ApptDate, OwnerName, MeetWithName, ID FROM appointment ORDER by date Desc; +----------------------+-----------+--------------+----+ | ApptDate | OwnerName | MeetWithName | ID | +----------------------+-----------+--------------+----+ | 08/25/04 12:15:00 PM | John | Mary | 6 | | 08/22/04 12:00:00 AM | Scott | Anna | 10 | | 08/12/04 11:00:00 AM | Shawn | Dr. Bell | 2 | | 08/11/04 11:15:00 AM | John | Mary | 5 | | 08/07/04 03:00:00 PM | Scott | James | 8 | | 08/06/04 12:00:00 AM | Shawn | Scott | 9 | | 08/05/04 02:00:00 PM | Shawn | Dr. Bell | 1 | | 08/04/04 12:15:00 PM | John | Mary | 4 | | 08/01/04 09:00:00 AM | Scott | David | 7 | | 07/31/04 12:15:00 PM | John | Mary | 3 | +----------------------+-----------+--------------+----+ 10 rows in set (0.02 sec) I have not needed to convert anything to any other thing in order to get my data sorted the way I wanted. I did convert the dates for display but NOT for sorting. Look at what happens if I add another date (let's say for sometime August of 2003) and sort by the formatted date string: INSERT appointment (OwnerName, MeetWithName, Date) Value ('Shawn', 'Scott','2003-08-07 16:00:00'); SELECT DATE_FORMAT(date, '%m/%d/%y %r') as ApptDate, OwnerName, MeetWithName, ID FROM appointment ORDER by ApptDate Desc; +----------------------+-----------+--------------+----+ | ApptDate | OwnerName | MeetWithName | ID | +----------------------+-----------+--------------+----+ | 08/25/04 12:15:00 PM | John | Mary | 6 | | 08/22/04 12:00:00 AM | Scott | Anna | 10 | | 08/12/04 11:00:00 AM | Shawn | Dr. Bell | 2 | | 08/11/04 11:15:00 AM | John | Mary | 5 | | 08/07/04 03:00:00 PM | Scott | James | 8 | | 08/07/03 04:00:00 PM | Shawn | Scott | 11 | | 08/06/04 12:00:00 AM | Shawn | Scott | 9 | | 08/05/04 02:00:00 PM | Shawn | Dr. Bell | 1 | | 08/04/04 12:15:00 PM | John | Mary | 4 | | 08/01/04 09:00:00 AM | Scott | David | 7 | | 07/31/04 12:15:00 PM | John | Mary | 3 | +----------------------+-----------+--------------+----+ 11 rows in set (0.00 sec) Can you find the row where the ID=11? It's sorted *alphabetically* by date because the DATE_FORMAT() function converts the datetime values in the Date field to strings. But if I had sorted on the datetime values themselves (without conversion) I would have: SELECT DATE_FORMAT(date, '%m/%d/%y %r') as ApptDate, OwnerName, MeetWithName, ID FROM appointment ORDER by Date Desc; +----------------------+-----------+--------------+----+ | ApptDate | OwnerName | MeetWithName | ID | +----------------------+-----------+--------------+----+ | 08/25/04 12:15:00 PM | John | Mary | 6 | | 08/22/04 12:00:00 AM | Scott | Anna | 10 | | 08/12/04 11:00:00 AM | Shawn | Dr. Bell | 2 | | 08/11/04 11:15:00 AM | John | Mary | 5 | | 08/07/04 03:00:00 PM | Scott | James | 8 | | 08/06/04 12:00:00 AM | Shawn | Scott | 9 | | 08/05/04 02:00:00 PM | Shawn | Dr. Bell | 1 | | 08/04/04 12:15:00 PM | John | Mary | 4 | | 08/01/04 09:00:00 AM | Scott | David | 7 | | 07/31/04 12:15:00 PM | John | Mary | 3 | | 08/07/03 04:00:00 PM | Shawn | Scott | 11 | +----------------------+-----------+--------------+----+ 11 rows in set (0.00 sec) Now, the oldest record is back on the bottom( where it should be, right?). You don't need to convert anything under most circumstances in order to get things sorted into the order you want them to be. You usually only need to convert for display (output). Notice I didn't query for the Date column but I was still able sort my records with it? Please, let me know what it is you need to convert to a numerical value and why you need to convert it. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -----8<---- previous responses clipped for space ------8<-------
