Oracle中日期格式转化之dd-M月-yyyy转为yyyyMMdd

今天药忘吃喽~ 2023-06-30 04:29 8阅读 0赞

### Oracle中日期格式转化之dd-M月-yyyy转为yyyyMMdd ###

*  问题:
 *   *  解决办法

# 问题: #

项目中遇到某张表的日期字段来自邮件解析入库，而表的日期字段又建的是nvarchar2类型，遂查询出的日期字段全是dd-M月-yyyy格式，对日期排序等操作非常不友好，需要转换乘yyyyMMdd  
![在这里插入图片描述][2020011511150013.png]

## 解决办法 ##

后半段借鉴网友出的同样问题，月份数字是动态的，需要case when判断转化。

SELECT
    	CASE
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%一%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'01'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%二%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'02'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%三%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'03'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%四%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'04'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%五%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'05'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%六%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'06'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%七%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'07'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%八%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'08'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%九%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'09'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%十%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'10'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%十一%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'11'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    WHEN SUBSTR (LAYDAYS_COMMENCING, 7, 2) LIKE '%十二%' THEN
    	CONCAT (
    		CONCAT (
    			SUBSTR (LAYDAYS_COMMENCING, 0, 4),
    			'12'
    		),
    		SUBSTR (LAYDAYS_COMMENCING, 9, 2)
    	)
    END AS LAYDAYS_COMMENCING
    FROM
    	(
    		SELECT
    			'20' || SUBSTR (
    				T .LAYDAYS_COMMENCING,
    				INSTR (
    					T .LAYDAYS_COMMENCING,
    					'-',
    					1,
    					2
    				) + 1,
    				2
    			) || '-' || DECODE (
    				LENGTH (
    					SUBSTR (
    						T .LAYDAYS_COMMENCING,
    						INSTR (
    							T .LAYDAYS_COMMENCING,
    							'-',
    							1,
    							1
    						) + 1,
    						LENGTH (
    							SUBSTR (
    								T .LAYDAYS_COMMENCING,
    								INSTR (
    									T .LAYDAYS_COMMENCING,
    									'-',
    									1,
    									1
    								) + 1
    							)
    						) - LENGTH (
    							SUBSTR (
    								T .LAYDAYS_COMMENCING,
    								INSTR (
    									T .LAYDAYS_COMMENCING,
    									'月',
    									1,
    									1
    								)
    							)
    						)
    					)
    				),
    				1,
    				'0'
    			) || SUBSTR (
    				T .LAYDAYS_COMMENCING,
    				INSTR (
    					T .LAYDAYS_COMMENCING,
    					'-',
    					1,
    					1
    				) + 1,
    				LENGTH (
    					SUBSTR (
    						T .LAYDAYS_COMMENCING,
    						INSTR (
    							T .LAYDAYS_COMMENCING,
    							'-',
    							1,
    							1
    						) + 1
    					)
    				) - LENGTH (
    					SUBSTR (
    						T .LAYDAYS_COMMENCING,
    						INSTR (
    							T .LAYDAYS_COMMENCING,
    							'月',
    							1,
    							1
    						)
    					)
    				)
    			) || '-' || SUBSTR (T .LAYDAYS_COMMENCING, 1, 2) AS LAYDAYS_COMMENCING
    		FROM
    			SHIPING T
    	)

![在这里插入图片描述][20200115112031741.png]

[2020011511150013.png]: https://img-blog.csdnimg.cn/2020011511150013.png
[20200115112031741.png]: https://img-blog.csdnimg.cn/20200115112031741.png