How to Convert 2 Strings To Date in MySQL

Question

i have a table tbl_remit.

 ________________________________________________________________
|RemitNo|ID|Employer|From_Month|From_Year|To_Month|To_Year|Amount|
|   1   |1 |    a   |   01     |   2016  |   01   |  2016 |200.00|
|   2   |1 |    a   |   02     |   2016  |   02   |  2016 |200.00|
|   3   |1 |    a   |   03     |   2016  |   03   |  2016 |200.00|
|   4   |1 |    a   |   04     |   2016  |   06   |  2016 |600.00|

This table represents the remittance of Employer(a) with ID(1) for the month of From_Month(01) From_Year(2016) To_Month(01) To_Year(2016) Amounting to Amount(200.00).

The employer paid 200.00 for the month of January 01, 2016 - January 31, 2016 . I need to create a view which combines the columns FROM_Month and FROM_Year as Date with the Format of 01/01/2016 and also columns TO_Month and TO_Year as Date with the Format of 01/31/2016.

I used STR_TO_DATE(Concat('From_To','/','1','/','From_Year'), '%m/%d/%Y') but it returns NULL

Any comments and suggestions are greatly appreciated.

Answer 1

Remove the single quotes around the From_To and From_Year columns:

SELECT STR_TO_DATE(CONCAT(From_Year, '/', From_Month, '/01'), '%Y/%m/%d') AS From_Date,
       STR_TO_DATE(CONCAT(To_Year,   '/', To_Month,   '/01'), '%Y/%m/%d') AS To_Date
FROM yourTable

As general advice, you should always store your date information in a single column, using a date type such as date or timestamp.

Update:

If you wanted to get the last day of the to date, you can use the LAST_DAY function:

LAST_DAY(STR_TO_DATE(CONCAT(To_Year, '/', To_Month, '/01'), '%Y/%m/%d'))