Transmogrify into date from multiple columns

What is a better way to program the following SQL:

str_to_date(
  concat(convert(D.DAY, CHAR(2)),
  '-',
  convert(M.MONTH, CHAR(2)),
  '-',
  convert(M.YEAR, CHAR(4))),
  '%e-%m-%Y' ) as AMOUNT_DATE

I want to convert the columns D.DAY, M.MONTH, and M.YEAR to a date field using MySQL. The above works, but seems much more complicated than开发者_Go百科 it needs to be. (If there's an ANSI SQL way to do it, that would be even better.)

Thank you!

One small improvement you can make is using CONCAT_WS instead of CONCAT (the "WS" stands for "with separator"--it's the equivalent of join or implode in other languages). And MySQL should automatically convert the arguments to strings, so the CONVERT functions are, I believe, unnecessary.

STR_TO_DATE(
  CONCAT_WS('-', D.DAY, M.MONTH, M.YEAR),
  '%e-%m-%Y'
) AS AMOUNT_DATE

I'm guessing this doesn't perform as well as Tomalak's arithmetic solution, though.

What about

DATE_ADD(
  DATE_ADD(
    MAKEDATE(M.YEAR,1),
    INTERVAL M.MONTH-1 MONTH
  ), 
  INTERVAL D.DAY-1 DAY
)