Oracle常用日期操作

最新推荐文章于 2023-09-24 22:22:16 发布

数仓大山哥

最新推荐文章于 2023-09-24 22:22:16 发布

阅读量533

点赞数

分类专栏： Oracle PLSQL 文章标签： ORACLE 日期处理正则

本文链接：https://blog.youkuaiyun.com/panfelix/article/details/103576611

版权

Oracle PLSQL 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍Oracle数据库中日期格式转换、日期列表生成及日期解析的方法。包括如何将'MON-YY'格式转换为标准日期，如何生成指定月份间隔的日期列表，以及如何从多个日期的逗号分隔字符串中使用正则表达式提取单个日期。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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';