Creating Date Objects from Disparate Date Fields in MySQL

Your predicament, where a database schema's date representation is split into individual day, month, and year fields, presents a unique challenge for date manipulation in SQL. However, using the following approach, you can effectively construct date objects from these separate fields.

To create a DATETIME object from integer values representing year, month, and day, utilize the combination of MAKEDATE() and DATE_ADD() functions.

1. MAKEDATE(): This function combines year and month (with a constant day of 1) to produce a DATETIME object representing the first day of the specified year.
2. DATE_ADD(): This function adds a specified number of months and days (or other specified time intervals) to a given DATETIME object.

By combining these functions, you can construct dates gradually:

• Start with MAKEDATE() to obtain the DATETIME object for the first day of the year.
• Use DATE_ADD() to add the month (subtracting 1 to account for its integer representation starting at 1).
• Apply another DATE_ADD() to add the day (again, subtracting 1 to adjust for its integer nature).

Example:

SELECT DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY);

This query returns the DATETIME object for March 11, 2013.

Now, you can employ this technique to construct date ranges for comparison in your SQL queries:

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 retrieves all records from the date table with dates falling between January 1, 2013, and January 1, 2014.