47
votes

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;
6
oh yuk. why do people do that to their databases? :-(Spudley
I actually ended up altering the database table to create a datetime field and then concatenate the year, month, day fields and update the rows based on deposition_id. Seems to have worked a treat.TGuimond
@Spudley sadly this is not uncommon... even 'crunchbase' api returns dates this way... it sucks... :-(Optimus
@Spudley because MySQL's datetime and timestamp types, and timezone conversions you cannot opt out of are still garbage after all these years.Michael Fulton
@MichaelFulton You may be right that there are issues with them. But if you're telling me that using a datetime field or timestamp is worse than the solution described in the question, then I'm afraid I will have to respectfully disagree.Spudley

6 Answers

55
votes

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

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_date
WHERE 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.

17
votes

The simplest way to do this is:

DATE(CONCAT_WS('-', year, month, day))

LPAD is not necessary as @pbarney pointed out earlier. If you are comparing with another date object, it's not strictly necessary to wrap it with DATE as MySQL will cast it automatically:

some_date_field > CONCAT_WS('-', year, month, day)

0
votes

Try to use CONCAT() and make it one field and compare .

Am not sure you can compare it as date after concatenation.

You can compare as integer.

concatinate year month day and make an integer like this 20101017 and compare.

Hopefully :)

0
votes

Expanding this answer, 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)
;