mysql查询查找比今天日期少的数据

Question

my date is stored in database 11/01/2015 12:00 AM - 11/01/2015 11:59 PM As Event_duration and i the first date is event starting date and after '-' is event ending date and i want to echo count of active event so i want to compare end date  11/01/2015 11:59 PM from today date time in my sql query to print in active event count please help me with this what should be the query to find active event count after exploding the date in database after dash '-' please give me the proper solution for this
i m trying this code

它正在产生以下输出: 11/02/2015 13:13 下午 < 11/03/2015 11:59 下午 11/02/2015 13:13 下午 < 11/03/2015 11:59 下午 11/02/2015 13:13 下午 < 11/27/2015 11:59 下午 11/02/2015 13:13 PM < 11/01/2015 11:59 PM 11/02/2015 13:13 PM < 12/01/2015 12:00 AM 但我无法比较日期:(请给我解决方案

     <?php
                         echo $Qry_Act_Count="SELECT SPLIT_STR(Event_Duration, '-', 2) as Active_Event_Time_Show from create_event";
                            $Act_Event_Count=0;
                            $Act_Count3=mysqli_query($con,$Qry_Act_Count);

                           date_default_timezone_set("Asia/Calcutta");
                           $Act_date = date("m/d/Y H:i A");

                           while ($Act_Rows=mysqli_fetch_assoc($Act_Count3))
                            {
                                  echo '<br>' . $Act_date  . '>' . $Act_Rows['Active_Event_Time_Show'];
                                  if($Act_date <= $Act_Rows['Active_Event_Time_Show']){

                                         echo $Act_Event_Count++;
                                        }
                                          }
                                                ?>

Answer 1

您想使用SUBSTRING_INDEX( ) 获取适当的日期，然后使用 STR_TO_DATE( ) 将其转换为有效日期就像这样

SELECT whatever
FROM table
WHERE STR_TO_DATE(TRIM(SUBSTRING_INDEX(my_date, '-', -1)),'%m/%d/%Y %r') < NOW()

分解它

SUBSTRING_INDEX(my_date, '-', -1) 表示给我可以由分隔符“-”分割的最后一个元素

TRIM() 只是删除任何额外的空格，因为您已经

STR_TO_DATE() 将该字符串转换为特定格式的日期。在你的情况下，你想要月/日/年

%r 用于说明文档中的晚上 11:59

%r - Time in 12 hour AM or PM format (hh:mm:ss AM/PM)

如果您愿意，可以将 %r 替换为 %h:%i %p 但我更喜欢 %r 因为它更干净、更少要编写的代码:)