Create date from day, month, year fields in MySQL

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

You can use STR_TO_DATE() function.

To build a sortable date string from that, you'll need CONCAT to join the bits together and LPAD to make sure the month and day fields are two digits long. Something like this:

CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))

Once you have that, you should be able to use BETWEEN, as they'll be in a sortable format. However if you still need to convert them to actual datetime fields, you can wrap the whole thing in UNIX_TIMESTAMP() to get a timestamp value.

So you'd end up with something like this:

SELECT UNIX_TIMESTAMP(CONCAT(`year`,'-',LPAD(`month`,2,'00'),'-',LPAD(`day`,2,'00'))) as u_dateWHERE u_date BETWEEN timestamp_1 and timestamp_2

However, be aware that this will be massively slower than if the field was just a simple timestamp in the first place. And you should definitely make sure you have an index on the year, month and day fields.