How to Construct a Date in MySQL from Separate Day, Month, and Year Fields?

Creating a Date from Individual Day, Month, and Year Fields in MySQL

In database applications, the need often arises to construct dates from separate day, month, and year fields for comparison or other processing. This becomes particularly relevant when the database schema is inherited from a previous design that chose to store date components in a non-standard manner.

Problem Statement (Revised)

This question pertains to creating a date object in SQL from a table with individual fields for 'day,' 'month,' and 'year' rather than a consolidated date field. The objective is to compare these dates with user-input ranges using the BETWEEN clause.

Database Schema

The following is the provided schema for the 'date' table:

CREATE TABLE IF NOT EXISTS `date` (
  `deposition_id` varchar(11) NOT NULL default '',
  `day` int(2) default NULL,
  `month` int(2) default NULL,
  `year` int(4) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Solution

To create a date object from these individual components, the following SQL logic can be employed:

1. Construct a Date from Year and Month: Use the MAKEDATE() function with a constant day of 1 to obtain a DATETIME representing the first day of the specified year and month.
2. Adjust Date with Month and Day: Apply the DATE_ADD() function to add the month and day values accordingly to the initial datetime.

Example Query

To select all rows in the 'date' table where the constructed date falls within a specific range:

SELECT * FROM `date`
WHERE DATE_ADD(DATE_ADD(MAKEDATE(year, 1), INTERVAL (month)-1 MONTH), INTERVAL (day)-1 DAY)
BETWEEN '2013-01-01' AND '2014-01-01';

This query will return all rows where the combined date constructed from the individual fields is between the two provided dates.

The above is the detailed content of How to Construct a Date in MySQL from Separate Day, Month, and Year Fields?. For more information, please follow other related articles on the PHP Chinese website!