Clorae Clorae - 1 month ago 6
MySQL Question

How to Convert 2 Strings To Date in MySQL

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

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'))
Comments