Create date from day, month, year fields in MySQL

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 1

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

Answer 2

You can use STR_TO_DATE() function.