1

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.

Clorae
  • 73
  • 7
  • 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 Oct 25 '16 at 02:51

1 Answers1

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'))
Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
  • Sir, How can i get the last day of the month for the `To_Date`? – Clorae Oct 25 '16 at 03:12
  • `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 Oct 25 '16 at 03:16
  • `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`. – Clorae Oct 25 '16 at 03:39
  • 1
    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 Oct 25 '16 at 03:43
  • I know, I was trying my best, but when I saw your query. I felt so stupid. Thank you so much sir. – Clorae Oct 25 '16 at 03:59
  • 1
    @Clorae Not stupid...writing complex SQL queries correctly can be a real torture, especially if you are new to it. Glad to help you :-) – Tim Biegeleisen Oct 25 '16 at 03:59