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.

You should be storing your date information using a date type (date, timestamp, etc.). But besides this, why do you need an actual date here? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 25, 2016 at 2:51

Tim Biegeleisen · Accepted Answer · 2016-10-25 03:16:20Z

3

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

edited Oct 25, 2016 at 3:16

answered Oct 25, 2016 at 2:53

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Clorae Over a year ago

Sir, How can i get the last day of the month for the To_Date?

Clorae Over a year ago

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

Clorae Over a year ago

STR_TO_DATE(CONCAT(To_Year, '/', To_Month, '/', LAST_DAY(STR_TO_DATE(CONCAT(To_Year, '/', To_Month, '/01'), '%Y/%m/%d'))), '%Y/%m/%d') AS To_Date FROM tbl_remit

I used this to get the Last day of the month but the result is like this 2016-01-20.

Tim Biegeleisen Over a year ago

Your query makes no sense to me, and this isn't what I recommended doing. Just wrap the STR_TO_DATE term from my query with LAST_DAY.

Tim Biegeleisen Over a year ago

@Clorae Not stupid...writing complex SQL queries correctly can be a real torture, especially if you are new to it. Glad to help you :-)

|

Collectives™ on Stack Overflow

How to Convert 2 Strings To Date in MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related