Mysql - 如何使用 am/pm 对日期格式进行排序

Question

请检查查询。

SELECT DATE_FORMAT( CAST( CONCAT( table.date,  " ", table.time ) ) ,  '%p' ) 
FROM table AS table

Error:
[hash]1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), '%p') FROM exp_visitorsday as vd LIMIT 0, 30' at line 2

我在以下情况下尝试过此查询。

SELECT DATE_FORMAT( CAST( CONCAT( table.date,  " ", table.time ) as datetimecol ) as datetimecol,  '%p' ) 
FROM table AS table

SELECT DATE_FORMAT( CAST( CONCAT( table.date,  " ", table.time ) as datetimecol ),  '%p' ) 
FROM table AS table

示例数据:

table.date => 01-08-2013
table.time => 10:00 AM

如何查询？这个查询有什么问题？

Answer 1

您可以:

SELECT * 
FROM (
    SELECT DATE_FORMAT( 
                                    CONCAT( SUBSTRING_INDEX( "01-08-2013", "-", -1), "-",  
                                    SUBSTRING_INDEX(SUBSTRING_INDEX( "01-08-2013", "-", 2), "-", -1), "-", 
                                    SUBSTRING_INDEX( "01-08-2013", "-", 1), " ", 
                                    IF( SUBSTRING_INDEX( "10:00 PM", " ", -1) = "AM", 
                                                SUBSTRING_INDEX( "10:00 AM", " ", 1),  
                                                CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX( "10:00 AM", " ", 1), ":", 1) + 12, ":", SUBSTRING_INDEX(SUBSTRING_INDEX( "10:00 AM", " ", 1), ":", -1))  )) , '%Y-%m-%d %h:%i') AS datformat ) AS tmp
ORDER BY tmp.datformat;

解释:

SELECT SUBSTRING_INDEX( "01-08-2013", "-", 1); #Day
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( "01-08-2013", "-", 2), "-", -1);#Month
SELECT SUBSTRING_INDEX( "01-08-2013", "-", -1); #Year