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;

Solution 1

You can use STR_TO_DATE() function.

Solution 2

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

Solution 3

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)

Solution 4

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.

View more solutions

Comments

[SEoF]

Certainly more elegant than creating and using strings.

[jon_darkstar]

good solution - why doesn't MySQL have this native? seems like this is a far more likely usecase than year with 100 or whatever days

[Soma Holiday]

STR_TO_DATE(CONCAT(date_year,'-',LPAD(date_month,2,'00'),'-'‌​,LPAD(date_day,2,'00‌​')), '%Y-%m-%d')

[pbarney]

It's not necessary to LPAD the digits. STR_TO_DATE does that for you: STR_TO_DATE(CONCAT(date_year,'-',date_month,'-',date_day), '%Y-%m-%d')

[pbarney]

Not sure why SO is changing the single quote into an html entity right there.

[Monticola Explorator]

@pbarney I would like to add that STR_TO_DATE('1-1-1', '%Y-%m-%d') evaluates to the first of January 2001, at least on the MySQL DB where I tested it. If you don't like that behaviour, you have to LPAD the year component so you have STR_TO_DATE('0001-1-1', '%Y-%m-%d'), which returns the first of January of year 1. I don't know if there is any culture configuration or anything that you can change to avoid having to LPAD the year, but I have not found it.

[Monticola Explorator]

I prefer your solution. The only thing is that makedate(99,1) returns the first of January of 1999. Is there any way to make it return 01/01/0099?

[Virus721]

I ran into issues using MAKEDATE, because of the changing number of days in february (mostly). When you want to create a date based on the month and day (of the week) of another date using DAYOFYEAR in order to pass that day of year to MAKEDATE along with a different year, you sometimes obtain a date one day before or after in the previous year. However I suppose the last part of this answer doesn't have this issue..