CODEWITHSUNDEEP
mysqlsqldate
astroboy
astroboy

Reputation: 197

How to convert "YYYYMM" format to "YYYY-MM-00"?

I have a sample table in mysql

id     yearmonth     
1      202001        
2      202002
3      202011
4      202012
5      201912
6      201911

The desired table in mysql is

id     yearmonth  
 1     2020-01-00
 2     2020-02-00
 3     2020-11-00
 4     2020-12-00
 5     2019-12-00
 6     2019-11-00

As we don't have day value in yearmonth column in the first table, we can replace with a default value.

Looking for your help! Thanks in advance!

Upvotes: 0

Views: 2373

Answers (2)

FanoFN
FanoFN

Reputation: 7124

How about concatenate the yearmonth column data value with the default day value you want then use STR_TO_DATE to change to date format:

SELECT STR_TO_DATE(CONCAT(yearmonth,'01'), '%Y%m%d') FROM test;

Or using DATE_FORMAT:

SELECT DATE_FORMAT(CONCAT(yearmonth,'01'), '%Y-%m-%d') FROM test;

Fiddle

Upvotes: 0

Dark Knight
Dark Knight

Reputation: 6551

You can use STR_TO_DATE function for the same.

SELECT STR_TO_DATE('202001','%Y%m');
Output:
2020-01-00

If you want to set day as a default value. Just use DATE_FORMAT.

SELECT DATE_FORMAT(STR_TO_DATE('202001','%Y%m'), '%Y-%m-01');
Output:
2020-01-01

As @Akina mentioned that it is dependent on NO_ZERO_IN_DATE and might lead to null result. You can look for this alternative

SELECT CONCAT(LEFT('202010',4) , '-' , RIGHT('202010',2) , '-01');

If you want it to date type then you can use CAST explicit.

SELECT CAST( CONCAT(LEFT('202010',4) , '-' , RIGHT('202010',2) , '-01') AS DATE);

Upvotes: 1

Related Questions