oracle创建日历表，并写入数据

最新推荐文章于 2022-02-07 01:37:02 发布

csdn_新人一枚

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量293

点赞数

CC 4.0 BY-SA版权

分类专栏： 1 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/weixin_35940293/article/details/90299672

1 专栏收录该内容

15 篇文章

订阅专栏

博客主要讲述了创建日历表，并向其中写入数据的相关内容，涉及数据库操作方面的信息技术知识。

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

创建日历表，并写入数据

 -- Create table
create table tdate
(
  DT          DATE not null,
  YEAR        number(4),
  MONTH      VARCHAR2(4),
  DAY         VARCHAR2(4),
  WEEK         VARCHAR2(10),
  WEEKNUM         VARCHAR2(4),
  XQ   number(2),
  QR    number(2),
  bourse_week  VARCHAR2(4)
);

insert into tdate t (  t.dt,
                        t.year,
                        t.month,
                        t.day,
                        t.week,
                        t.weeknum,
                        t.xq,
                        t.qr,
                        t.bourse_week)
select trunc(everyDay) as dt,
   to_char(everyday,'yyyy') as yr,
   to_char(everyday,'mm') as mm,
   to_char(everyday,'dd') as dd,
   to_char(everyday,'dy') as dayofweek,
   /*ORACLE自定义的标准周*/
   to_char(everyday,'WW') as weeknum,
   /*该月的第几周*/
   /*lpad(to_char(everyday,'w'),6) as monthOfWeek,*/
   to_char(everyday,'d') XQ,
   to_char(everyday,'Q') as qr,
   /*ISO的标准周，通常使用这个*/
   to_char(everyday,'IW') as bourse_week
   from(select to_date('20170101','yyyymmdd') + level - 1 as everyDay from dual
  connect by level <= 
  (last_day(to_date('20220101','yyyymmdd')) - to_date('20170101','yyyymmdd') +1));

 

 SELECT * FROM (
with x as
  (select add_months(trunc(sysdate, 'y'), -12) + level - 1 tdate
      from dual
    connect by level <=
              add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y'))
 select tdate, to_char(tdate, 'day'),rownum rn from x where to_char(tdate, 'day') ='星期五'   )
 where    rn=20