Creating MySQL Dates From Integer day, month and year columns

Granted, this is hardly a common occurrence, but recently I’ve had need to work with a database table that for historical reasons stores dates in three separate integer columns, one each for day, month and year. The need for special intervention here depends very much on your data, and I’m assuming that if you’re reading this your powerless the change the actual data structure, otherwise you would. And that’s okay.

If your year columns are YYYY and your month and day columns are zero padded then your solution is an easy one. Simply concatenate the values together using MySQL’s CONCAT_WS function (concatenate with separator).

SELECT CONCAT_WS('-', '2001', '01','31')
--> '2001-01-31'

If this isn’t the case you could deal with the problem in code, using PHPs famously forgiving strtotime function for example, but my OCD got the better of me and I decided to revisit MySQL’s built in date and time functions

An Elegant Solution

The solution I found is to use the STR_TO_DATE function with the CONCAT_WS function. That way, even if the date parts aren’t correctly formatted as in the following example (2 digit year, 1 digit month), STR_TO_DATE will correctly interpret the string ‘1-1-31′ and return a datetime value of ‘2001-01-31′. This also has the advantage of being an actual DATETIME value rather than a string.

SELECT STR_TO_DATE(CONCAT_WS('-',1,1,31),'%Y-%m-%d') AS date
--> '2001-01-31'

Obviously, this is a solution to a problem that shouldn’t exist, but that’s just how we roll.