On Wed, Apr 20, 2005 at 10:55:31AM -0400, Russ Wright wrote:
> Hi all
>
> I inherited a MYSQL table where the StartDate and StartTime fields are
> VARCHAR. I am having a problem retrieving events for a specific date in
> the right time order.
>
> For example:
> SELECT StartDate, StartTime FROM `calendar` WHERE `StartDate` =
> '4/16/2005' order by `StartTime`
>
> Returns:
> STARTDATE STARTTIME
> ---------------------
> 4/16/2005 12:00:00 AM
> 4/16/2005 6:00:00 PM
> 4/16/2005 8:00:00 AM
>
> Notice that the events are not in the right order by time. How do I fix
> this?
No matter how you look at this, you're going to have to write some sort
of function to deal with this, or change the schema for this table.
Dates and times shouldn't be varchar for exactly this reason. They sort
alphabetically, which is what's happening here. I'd dump this table out,
recreate the table with the proper types for its fields, then re-insert
the records (unless MySQL lets you change field types in place ;-).
Otherwise, you can modify the records by reading out the starttime on
each record, padding it with a '0' if it's less than 11 characters, and
then update the record. But ultimately, I'd find a way to change the
field types, because otherwise you'll continue to have this problem.
Paul
-----------------------------------------------------------------------
This list is provided as an unmoderated internet service by Networked
Knowledge Systems (NKS). Views and opinions expressed in messages
posted are those of the author and do not necessarily reflect the
official policy or position of NKS or any of its employees.
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 17:53:05 EDT