1. 'MON-YY' 转换为日期
SELECT t.period_name,
TO_CHAR(TO_DATE(t.period_name,
'MON-YY',
'NLS_DATE_LANGUAGE = American'),
'YYYYMM')
FROM scott.test t;
2.日期月份间隔列表获取
select time_list
from (select TO_CHAR(add_months(to_date(201801, 'yyyymm'), level - 1),
'MON-YY',
'NLS_DATE_LANGUAGE = American') time_list
from dual
connect by level <=
months_between(to_date(201805, 'yyyymm'),
to_date(201801, 'yyyymm')) + 1)
3.多日期日期逗号分割,正则表达式获取单个日期
SELECT TO_CHAR(TO_DATE(time_list, 'YYYYMM'),
'MON-YY',
'NLS_DATE_LANGUAGE = American')
from (select SUBSTR(REGEXP_SUBSTR(time_list, '[^,]+', 1, LEVEL), 0, 6) time_list
from (SELECT replace('201511,201512,201601', '"', null) as time_list
FROM dual)
connect BY level <= regexp_count(time_list, ',') + 1
AND time_list = PRIOR time_list
AND PRIOR dbms_random.value IS NOT NULL);
select time_list
from (select TO_CHAR(add_months(to_date(&p_from_date_id, 'yyyymm'),
level - 1),
'YYYYMM') time_list
from dual
connect by level <=
months_between(to_date(&p_to_date_id, 'yyyymm'),
to_date(&p_from_date_id, 'yyyymm')) + 1);
select count(*)
-- into V_C1
from (select SUBSTR(REGEXP_SUBSTR(time_list, '[^,]+', 1, LEVEL), 0, 6) time_list
from (SELECT replace(P_DATE_LIST, '"', null) as time_list
FROM dual)
connect BY level <= regexp_count(time_list, ',') + 1
AND time_list = PRIOR time_list
AND PRIOR dbms_random.value IS NOT NULL);
P_DATE_LIST='201501,201502,201503';