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

问题:

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

解决办法

后半段借鉴网友出的同样问题，月份数字是动态的，需要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
	)