-1

So i have 2 column Year and Month and I want to take the month and the year to show in the new column and showing the last date of the month

Example : Year column = 2019, 2020, 2021 Month Column = Jan, Feb, Mar

i want the result : 31-01-2019, 29-02-2019, etc

forpas
  • 160,666
  • 10
  • 38
  • 76
Michael
  • 1
  • 5
  • Have you read the docs on date and time functions? https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html – Thorsten Kettner Jul 14 '21 at 14:30
  • Try LAST_DAY() function: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day – forpas Jul 14 '21 at 14:34
  • i have read those, im confused how to extract the month with MON format and the year from the column, and then putting those together with the last day of the month, since I'm new to SQL. i hope you can help me thanks! – Michael Jul 14 '21 at 14:34
  • Could use 12 `replace` statements for that.. e.g. `replace(replace(month, 'Jan', '01'), 'Feb', '02'))` – user3783243 Jul 14 '21 at 14:50
  • so i need to replace all the month first to digit? after that I use the last day function ?? – Michael Jul 14 '21 at 14:53

1 Answers1

2

First concatenate the year, the month and '01' to create a string date like:'2019-Jan-01'.
Then use STR_TO_DATE() to convert it to a valid date and finally use LAST_DAY() to get the last day of that year/momth:

SELECT LAST_DAY(STR_TO_DATE(CONCAT(year, '-', month, '-01'), '%Y-%b-%d')) date
FROM tablename

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • if the month and year data are in a huge amount, I need to change them 1 by 1 into string? Example : I have data from January 2019 until January 2021 – Michael Jul 14 '21 at 15:02
  • @Michael No. In the function concat() all numeric years will be implicitly converted to strings. – forpas Jul 14 '21 at 15:06
  • @Michael: No. *You* write just one query that contains the one conversion expression as shown. The *DBMS* will then apply this expression on every row in the table. – Thorsten Kettner Jul 14 '21 at 15:15
  • `LAST_DAY(STR_TO_DATE(CONCAT(TAHUN, '-', BULAN, '-01'), '%Y-%b-%d')) date AS PERIODE` i got error when i want to rename the header to PERIODE – Michael Jul 14 '21 at 16:04
  • @Michael: I used `date` for the alias. Remove it. – forpas Jul 14 '21 at 16:07
  • `00909. 00000 - "invalid number of arguments"` im getting this error – Michael Jul 15 '21 at 06:20
  • @Michael this is not a MySql error. I don't know how you use my query. – forpas Jul 15 '21 at 06:43