mysql中文日期处理以及将其转换为date类型笔记

最新推荐文章于 2024-07-29 04:03:43 发布

彼得暹罗猫

最新推荐文章于 2024-07-29 04:03:43 发布

阅读量2.3k

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/Gage_0209/article/details/82803027

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

首先介绍下要用到的几个函数，因为太久没写SQL了，截取字符串的时候就在想如何截取指定两个字符之间的内容，如将‘2018年9月10日’中的2018，9，10单独拎出来，网上搜了相关的问题发现可以结合SUBSTR()函数和INSTR()函数来截取字符串中指定字符之间的子字符串。

SUBSTR(str, pos, len): 其中str是要截取的字符串对象，pos为截取的开始位置，len为截取的字符串长度

SELECT SUBSTR('2017年9月10日', 1, 4);
+----------------------------------+
| SUBSTR('2017年9月10日', 1, 4)    |
+----------------------------------+
| 2017                             |
+----------------------------------+

INSTR(str, str_target)：其中str是被搜索的字符串对象；str_target是希望搜索的目标，即希望找到的指定字符；INSTR()返回目标字符在被搜索字符串中的索引

SELECT INSTR('2017年9月10日', '年');
+----------------------------------+
| INSTR('2017年9月10日', '年')     |
+----------------------------------+
|                                5 |
+----------------------------------+

这样，结合使用SUBSTR()函数和INSTR()函数就可以截取一段字符中指定字符之间的部分，如截取出’2017年9月10日’中年份和月份部分，可以用

SELECT SUBSTR('2017年9月10日', 1, INSTR('2017年9月10日', '年')-1) AS year;
+------+
| year |
+------+
| 2017 |
+------+
SELECT SUBSTR('2017年9月10日', INSTR('2017年9月10日', '年')+1, INSTR('2017年9月10日', '月')-INSTR('2017年9月10日', '年')-1) AS month;
+-------+
| month |
+-------+
| 9     |
+-------+

CONCAT(‘a’, ‘b’, ‘c’)：字符串拼接函数，将字符串对象a，b，c连接起来

SELECT CONCAT('2017','-','9','-','10') AS date;
+-----------+
| date      |
+-----------+
| 2017-9-10 |
+-----------+

STR_TO_DATE(str, format)：字符串类型转换为date类型的日期格式，其中str是待转换的字符串格式日期，format为要转换成的date型日期格式

SELECT STR_TO_DATE('2017-9-10', '%Y-%m-%d') AS date;
+------------+
| date       |
+------------+
| 2017-09-10 |
+------------+

有了如上基础，对于下面这样的SQL题就很简单了
在这里插入图片描述
将字符串日期中的年份，月份，天数分别用SUBSTR和INSTR解析出来，进行相应的处理，再拼接成字符串最后再利用字符串转date函数转换为date型日期，这个问题可以用如下SQL解决：

SELECT STR_TO_DATE(CONCAT(year,'-',month,'-',day),'%Y-%m-%d') as date 
      ,name
      ,score 
FROM ( SELECT CASE WHEN year LIKE '%2017%' THEN '2017'
              ELSE '2016' END AS year
             ,TRIM(month) AS month
             ,TRIM(day) AS day 
             ,name
             ,score 
       FROM( SELECT SUBSTR(DATE,1,INSTR(DATE,'年')-1) AS year
                   ,SUBSTR(DATE,INSTR(DATE,'年')+1,INSTR(DATE,'月')-INSTR(DATE,'年')-1) AS month
                   ,SUBSTR(DATE,INSTR(DATE,'月')+1,INSTR(DATE,'日')-INSTR(DATE,'月')-1) AS day
                   ,name
                   ,CASE WHEN score <= 100 THEN score 
                    ELSE 100 END AS score 
             FROM score) A) B;