I have a varchar field which has data as 9.50 pm, 8.59 am, 2.50 pm,4.00 am...... I want to sort this field in time format I tried cast(), convert() but not working. pls help me urgent!
i'd suggest reading http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
I would recommend having a new column in which data is stored in proper time format. For quick solution, ordering by below may help. SELECT col1, col2, ..., coln FROM tbl_name WHERE condition ORDER BY STR_TO_DATE(varchar_field, IF(RIGHT(varchar_field, 2) = 'am', '%h.%i am', '%h.%i pm')) ASC Code (markup): NOTE: Above way may slow down the query, because of run time conversion and then ordering. Best would be to have a separate column with proper time data and then direct ordering on it.
I tried your query SELECT * FROM test ORDER BY STR_TO_DATE( otime, IF( RIGHT( otime, 2 ) = 'am', '%h.%i am', '%h.%i pm' ) ) ASC LIMIT 0 , 30 and it is showing 8:20 PM 9:50 AM 8:00 AM 7:20 PM 5:00 PM 6:15 AM
If you wish the converted value in result as well, then you might want to alter your query like this: SELECT *, STR_TO_DATE( otime, IF( RIGHT( otime, 2 ) = 'am', '%h.%i am', '%h.%i pm' ) ) AS otime_converted FROM test ORDER BY STR_TO_DATE( otime, IF( RIGHT( otime, 2 ) = 'am', '%h.%i am', '%h.%i pm' ) ) ASC LIMIT 0 , 30 Code (markup):
If otime has values you mentioned, like 3.40 pm, 2.50 am, then above query should work proper. Does it have any space after text? In that case change RIGHT(otime, 2) with RIGHT(TRIM(otime), 2).