DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Date Comparison In MySQL

10.05.2010
| 1572 views |
  • submit to reddit
        // 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' ;