sql常用字段参数处理

1.传入日期yyyymmdd，转化为对应月份

select
	CASE SUBSTR(date, 5, 2)
		WHEN '01' THEN '1月份'
		WHEN '02' THEN '2月份'
		WHEN '03' THEN '3月份'
		WHEN '04' THEN '4月份'
		WHEN '05' THEN '5月份'
		WHEN '06' THEN '6月份'
		WHEN '07' THEN '7月份'
		WHEN '08' THEN '8月份'
		WHEN '09' THEN '9月份'
		WHEN '10' THEN '10月份'
		WHEN '11' THEN '11月份'
		WHEN '12' THEN '12月份'
		ELSE 'Invalid Month'
	END AS date
from table
GROUP BY date		
order by CAST(REPLACE(field_name, '月份', '') AS INTEGER); -- 此处排序月份

2.按照指定排序

order by
	case AREA
		WHEN 'XX区域' THEN 1
		WHEN 'XX区域' THEN 2
		WHEN 'XX区域' THEN 3
		WHEN 'XX区域' THEN 4
		WHEN 'XX区域' THEN 5
		WHEN 'XX区域' THEN 6
		else 7
	END

3.获取当年第一天

SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'YYYYMMDD') FROM DUAL;

4.获取当日日期

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL;

5.获取当年月份

select to_char(sysdate, 'yyyymm') from dual