On Wed, 2005-04-20 at 10:55 -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?
>
>
> Regards
> Russ
>
Mysql is ordering by VARCHAR's natural order, part of that being 1 comes
before 2, and so on, so that it is ordering alphabetically.
Alphabetically, 12 comes before 6, as "add" comes before "b".
Ideally what you would want to do is make the StartDate a DATE, and
StartTime a TIME, or combine them into one column which is DATETIME.
The problem is that you need to convert the current VARCHARs to DATEs
and TIMEs. Depending on your setup you could probably use the MYSQL
client to copy the data from the original table into a new table that
has DATE and TIME columns converting the VARCHARS to DATE or TIME as you
go, or you could write an app or script in you favorite language to read
from the old table, convert and save as DATE or TIME. Or you could do
it by hand which, unless it's a small amount of data, would be very
nasty.
If this query is being performed in an application, you could also
resort the query results in the app. That's kinda ugly, but would work,
Disclaimer:
The materials in this e-mail are private and may contain Protected Health Information. Please note that e-mail is not necessarily confidential or secure. Your use of e-mail constitutes your acknowledgment of these confidentiality and security limitations. If you are not the intended recipient, be advised that any unauthorized use, disclosure, copying, distribution, or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender via telephone or return e-mail.
-----------------------------------------------------------------------
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:15 EDT