Oracle创建时间维度
SQL Scripts to create Date Dimension in Oracle. Create Table SQL: CREATE TABLE Date_D( DateKey Integer NOT NULL, DateValue Date NOT NULL, Day Char(10 ), DayOfWeek Integer, DayOfMonth Integer, DayOfYear Integer, PreviousDay date, NextDay da
SQL Scripts to create Date Dimension in Oracle.
Create Table SQL:
CREATE
TABLE Date_D(
DateKey Integer NOT NULL,
DateValue Date NOT NULL,
Day Char(10 ),
DayOfWeek Integer,
DayOfMonth Integer,
DayOfYear Integer,
PreviousDay date,
NextDay date,
WeekOfYear Integer,
Month Char(10 ),
MonthOfYear Integer,
QuarterOfYear Integer,
Year Integer
)
SQL To populate the table:
INSERT
INTO Date_D
SELECT
to_number(to_char(CurrDate, 'YYYYMMDD')) as DateKey,
CurrDate AS DateValue,
TO_CHAR(CurrDate,'Day') as Day,
to_number(TO_CHAR(CurrDate,'D')) AS DayOfWeek,
to_number(TO_CHAR(CurrDate,'DD')) AS DayOfMonth,
to_number(TO_CHAR(CurrDate,'DDD')) AS DayOfYear,
CurrDate - 1 as PreviousDay,
CurrDate + 1 as NextDay,
to_number(TO_CHAR(CurrDate+1,'IW')) AS WeekOfYear,
TO_CHAR(CurrDate,'Month') AS Month,
to_number(TO_CHAR(CurrDate,'MM')) AS MonthofYear,
to_number((TO_CHAR(CurrDate,'Q'))) AS QuarterOfYear,
to_number(TO_CHAR(CurrDate,'YYYY')) AS Year
FROM (
select level n, TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') CurrDate
from dual
connect by level
order by 1
上一篇: 新手请问合法用户验证有关问题
下一篇: 一步步学习html----第四课