Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to show the next month using concat, month 12 (dec) following month became 13

I have a table like this.

employee_id join_year join_month joindatetime first_name last_name
1 2011 03 2011-03-06 06:00:00 Mike Jordan
2 2012 11 2012-11-19 07:00:00 John Simpson
31 2019 12 2019-12-05 11:30:00 Jane Athur

and I need to add a column using concat a couple of columns and
one special requirement is that join_month is to show the following month.

For example,
join_month is 03 (for Mike) + 1 (adding 1 to get the following month) so outcome should be 04.
and if join_month is 11 + 1 (following month) so outcome should be 12

so the expected outcome should be

1-20110401-Mike-Jordan
2-20121201-John-Simpson
31-20200101-Jane-Athur

so with my limited knowledge, I used the following method

concat(employee_id,'-',join_year,join_month+1,'01','-',firstname,'-',lastname) as 'WWID'

however, I am getting the following result.

1-2011401-Mike-Jordan
2-20121201-John-Simpson
31-20191301-Jane-Athur

2 issues here.

1st issue : 2 digits of month is becoming 1 digit for the first line (04 > 4)

2nd issue : following month of 201912 (dec) should be following year of 202001 (jan) but it comes out as 201913 instead..

is there any way to achieve above expeced outcome? Pleae help.

Thank you

Tags:

3

Answers


  1. 0

    You need to handle the special case when the month is 12. The month number wraps around to 1, and the year has to increment. You can use IF or CASE for this.

    concat(employee_id,'-',
    IF(join_month = 12, join_year+1, join_year),
    IF(join_month = 12, 1, join_month+1),
    '01','-',firstname,'-',lastname) as 'WWID'
    Login or Signup to reply.
  2. 0 
    mysql> select extract(year_month from date_add('2019-12-05', interval 1 month)) as ym;
+--------+
| ym     |
+--------+
| 202001 |
+--------+
    Login or Signup to reply.
  3. 0 
    WITH tbl (employee_id, join_year, join_month, joindatetime, first_name, last_name) AS (
    VALUES
        ROW( 1, 2011, 03, '2011-03-06 06:00:00', 'Mike', 'Jordan'),
        ROW( 2, 2012, 11, '2012-11-19 07:00:00', 'John', 'Simpson'),
        ROW(31, 2019, 12, '2019-12-05 11:30:00', 'Jane', 'Athur')
)
SELECT CONCAT_WS('-', employee_id, DATE_FORMAT(joindatetime + INTERVAL 1 MONTH, '%Y%m01'), first_name, last_name) AS WWID
FROM tbl;

    Outputs:

    WWID
    1-20110401-Mike-Jordan
    2-20121201-John-Simpson
    31-20200101-Jane-Athur
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search