CODEWITHSUNDEEP
mysql
Arpit Pathak
Arpit Pathak

Reputation: 63

mysql query to find data less then today date

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

and it is producing this output: 11/02/2015 13:13 PM < 11/03/2015 11:59 PM 11/02/2015 13:13 PM < 11/03/2015 11:59 PM 11/02/2015 13:13 PM < 11/27/2015 11:59 PM 11/02/2015 13:13 PM < 11/01/2015 11:59 PM 11/02/2015 13:13 PM < 12/01/2015 12:00 AM but Im not able to compare date :( Please give me solution 

     <?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++;
                                        }
                                          }
                                                ?>

Upvotes: 0

Views: 507

Answers (2)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

You need some steps for this process :-

First use SUBSTRING_INDEX

SUBSTRING_INDEX('11/01/2015 12:00 AM-11/01/2015 11:59 PM','-',-1)

second use Str_to_date

Str_to_date(
SUBSTRING_INDEX('11/01/2015 12:00 AM-11/01/2015 11:59 PM','-',1),'%m/%d/%Y %h:%i')

Try this query:-

SELECT * FROM table
 WHERE Str_to_date(
SUBSTRING_INDEX('11/01/2015 12:00 AM-11/01/2015 11:59 PM','-',1),'%m/%d/%Y %h:%i') 
< NOW()

or use interval 12 HOUR for get 24 hours format 

SELECT * FROM table WHERE 
str_to_date(SUBSTRING_INDEX('11/01/2015 12:00 AM-11/01/2015 1:59 PM','-',-1),'%m/%d/%Y %h:%i')
+ interval 12 HOUR < NOW()

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25872

you want to use SUBSTRING_INDEX( ) to get the appropriate date and then cast it to a valid date with STR_TO_DATE( ) like so

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

to break it down

SUBSTRING_INDEX(my_date, '-', -1) says give me the last element that can be split by the delimiter '-'

TRIM() just removes any additional whitespace since you have

STR_TO_DATE() converts that string to a date of a particular format. in your case you want it month/day/year

the %r is to account for the 11:59 PM from the docs

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

if you wanted you could replace %r with %h:%i %p but I prefer %r since its cleaner and less code to write :)

Upvotes: 2

Related Questions