如何填充Oracle中的日历表？ [英] How to populate calendar table in Oracle?

问题描述

我想在Oracle DB中维护一个日历表，我希望从2011年到2013年（可能一直到任何一年）的一年中的所有日期填充。

I want to maintain a calender table in Oracle DB which I want to populate with all the days of the year starting from 2011 to 2013 (it may be till any year). How can I do that?

考虑我的数据库表有列和示例数据集：

Consider my DB table has columns and example dataset is:

S.No  Cal_Dt      DayName 
1     01-01-2011  Monday
2     02-01-2011  Tuesday
3     03-01-2011  Wednesday

等。

我更关心Cal_Dt这里（DayName是可选的）。

I am more concerned with the Cal_Dt only here (DayName is optional).

推荐答案

这是一个简单而容易的方法。

This is a simple and easy way to do it

with calendar as (
        select :startdate + rownum - 1 as day
        from dual
        connect by rownum < :enddate - :startdate
    )
select rownum as "S.No", to_date(day,'dd_mm_yyyy') as "Cal_Dt", to_char(day,'day') as "DayName"
from calendar

这篇关于如何填充Oracle中的日历表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！