Mysql 5.1.30-community / getting datetime in 24hr format from 2 cols for correct sort

Databases
1

Hi All,

i have a very typical problem with my database but i m unable to find a solution. any help is greatly appreciated, here is the problem:

EVENT TABLE SCHEMA AND DATA:

AutoID, EventDateTime(datetime), EventAMPM(varchar(2))
21915, 2010-09-12 09:30:00, PM
21916, 2010-09-12 10:30:00, AM
21917, 2010-09-12 00:00:00, AM

As you can see that if i sort result based on EventDateTime column, i will get incorrect sorting because 9:30 will come up above 10:30 which is incorrect if we consider the EventAMPM field. How can i get correct sorting on this table based on event date & time? I guess if i am able to concat these two cols and then convert it to datetime 24hr format, it will solve my problem but i dont know the mysql syntax :frowning:

TIA

2

just tried ur order by code and it worked like a charm but i have to replace ‘PM’ with ‘AM’ in your code otherwise PM date/time was coming before AM. anyways, THANKS for the help :nanaman:

the initial db design was not done by me otherwise i wouldn’t have done such a mistake, its a legacy app that i m working on :stuck_out_tongue:

3

okay, i understand, but at least now you have learned what a lame and useless decision it was to do it that way, and you’ll never do something like this yourself

ORDER
    BY CASE WHEN EventAMPM = 'PM'
            THEN EventDateTime
            ELSE EventDateTime + INTERVAL 12 HOUR
        END
4

thanks for the post but i am not subtracting 12hrs to get 9:30. its a cold fusion app (developed by someone else :)) that is saving date & time this way. it is saving date & time in 12 hr format in one column (EventDateTime) and saving AM/PM in another column (EventAMPM). i cant change the db schema to combine these two columns. my only option is to use mysql query to concate these columns and then convert them in to 24 hr format to get the correctly sorted data. is it possible to do so with mysql query only?

5

you have two columns to store a single value – that’s your problem right there

from your post, i would assume that you took a datetime value like ‘2010-09-12 21:30:00’ and somehow converted this to ‘2010-09-12 09:30:00’ (by subtracting 12 hours??) and this is needless, and problematic, extra work

you need to convert your datetimes back to accurate datetimes and ditch the AMPM column