How to convert string to date and calculate using mysql-CodePudding

I have a column that storage the year and month like this

start  | end
202101 | 202212
201905 | 202001

The format is YYYYMM

I need to convert the two columns in date and get the difference in months between 'start' and 'end' columns

CodePudding user response：

I see, you get 2021-01-00 if you just use str_to_date(), which makes the date invalid to use for datediff() or timestampdiff().

mysql> select * from t;
 -------- -------- 
| start  | end    |
 -------- -------- 
| 202101 | 202212 |
| 201905 | 202001 |
 -------- -------- 

mysql> select str_to_date(start, '%Y%m') as start,
    str_to_date(end, '%Y%m') as end from t;
 ------------ ------------ 
| start      | end        |
 ------------ ------------ 
| 2021-01-00 | 2022-12-00 |
| 2019-05-00 | 2020-01-00 |
 ------------ ------------

You can fix this by appending '01':

mysql> select str_to_date(concat(start, '01'), '%Y%m%d') as start,
    str_to_date(concat(end, '01'), '%Y%m%d') as end from t;
 ------------ ------------ 
| start      | end        |
 ------------ ------------ 
| 2021-01-01 | 2022-12-01 |
| 2019-05-01 | 2020-01-01 |
 ------------ ------------ 

mysql> select timestampdiff(month, str_to_date(concat(start, '01'), '%Y%m%d'), 
    str_to_date(concat(end, '01'), '%Y%m%d')) as diff from t;
 ------ 
| diff |
 ------ 
|   23 |
|    8 |
 ------

CodePudding user response：

use str_to_date

select datediff(str_to_date(end, '%Y%m'), str_to_date(start, '%Y%m'))

or for a valid day (month begin)

select datediff(str_to_date(end, '%Y%m01'), str_to_date(start, '%Y%m01'))