MySQL YYYYMMDDHHMMSS to datetime

1 개요

MySQL YYYYMMDDHHMMSS to datetime

MySQL

Copy

SELECT REGEXP_REPLACE("20171117003140", '([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})', '\\1-\\2-\\3 \\4:\\5:\\6')
-- 2017-11-17 00:31:40

MySQL

Copy

SET @x="20171117003140";
SELECT CONCAT(SUBSTRING(@x,1,4),"-",SUBSTRING(@x,5,2),"-",SUBSTRING(@x,7,2)," ",SUBSTRING(@x,9,2),":",SUBSTRING(@x,11,2),":",SUBSTRING(@x,13,2))
-- 2017-11-17 00:31:40

2 같이 보기

• MySQL CONCAT()
• MySQL SUBSTRING()
• MySQL REGEXP_REPLACE()
• MySQL datetime to YYYYMMDDHHMMSS
• YYYYMMDDHHMMSS to datetime