Date Comparison In MySQL

• Tweet
• 

        // Make use of the following logic while executing queries which needs to bring the result-sets based on a date // range criteria . e.g period_from, period_to
//
// Consider a table with the following fields 
// ------------------------------------------------------------------------------------------
// tbl_contests (
//               id int(11),
//               contest_name varchar(255),
//               period_from date,
//               period_to date,
//               status char(1)
//              );

// Lets say each contest entry has a record in this table(Normally all the fields should be entered, but in // some situations, the period_to may be skipped, for contests with never ending mode. 
// Assume that you want to retrieve all the active contests on the current date, keeping in mind for contests
// that do not have any period_to entered to also to be considered.
//
// Use the following SQL code to retrieve the desired result set.
// ------------------------------------------------------------------------------------------

         SELECT * FROM tbl_contests 
         WHERE 
              CURDATE() BETWEEN period_from AND IF(period_to='0000-00-00',CURDATE(),period_to) 
         AND status= 'Y' ;

Tags:
• Date Comparison in MySQL