Question: I am currently developing an application that displays documents and allows the members to search for these documents by a number of different parameters, one of them being date range.

The problem I am having is that the database schema was not developed by myself and the creator of the database has created a 'date' table with fields for 'day','month','year'.

I would like to know how I can select a specific day, month, year from the table and create a date object in SQL so that I can compare dates input by the user using BETWEEN.

Below is the structure of 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;

```

Answer: 
When you have integer values for year, month and day you can make a DATETIME by combining MAKEDATE() and DATE\_ADD(). MAKEDATE() with a constant day of 1 will give you a DATETIME for the first day of the given year, and then you can add to it the month and day with DATE\_ADD():

```
mysql> SELECT MAKEDATE(2013, 1);
+-------------------+
| MAKEDATE(2013, 1) |
+-------------------+
| 2013-01-01        |
+-------------------+

mysql> SELECT DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH);
+---------------------------------------------------+
| DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH) |
+---------------------------------------------------+
| 2013-03-01                                        |
+---------------------------------------------------+

mysql> SELECT DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY);
| DATE_ADD(DATE_ADD(MAKEDATE(2013, 1), INTERVAL (3)-1 MONTH), INTERVAL (11)-1 DAY) |
+----------------------------------------------------------------------------------+
| 2013-03-11                                                                       |
+----------------------------------------------------------------------------------+

```

So to answer the OP's question:

```
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';

```
Expanding [this answer](https://stackoverflow.com/a/20746923/7032856), here's my take on it:

```
DELIMITER $$

CREATE FUNCTION fn_year_month_to_date(var_year INTEGER,
    var_month enum('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12')
    )
RETURNS DATE
BEGIN
    RETURN (MAKEDATE(var_year, 1) + INTERVAL (var_month - 1) MONTH);
END $$

DELIMITER ;

SELECT fn_year_month_to_date(2020, 12)
;

```