Home » SQL & PL/SQL » SQL & PL/SQL » time dimension loading week startign from saturday
time dimension loading week startign from saturday [message #218010] Tue, 06 February 2007 06:38 Go to next message
Messages: 13
Registered: November 2006
Location: Jeddah Saudi Arabia
Junior Member

asslam o alaikum all,
please help in loading the time dimension i dont have any script to load it. i have this small script but this is not fullfilling my needs.
first one is the week should start from saturday in dimension
and i need also the week number of month and week number of year

second thing same fields for fiscal year also required
i have this script

trunc(sysdate) the_date,
sysdate date_time,
to_char(sysdate,'YYYY') Year,
to_char(sysdate,'Year') Year_spelled,
to_char(sysdate,'YY') short_year,
to_char(sysdate,'YYYY-Q') Year_quarter,
to_char(sysdate,'YYYY-Q') quarter,
to_char(sysdate,'MM') Month,
to_char(sysdate,'Month') Month_spelled,
to_char(sysdate,'YYYY-Q-MM') Year_quarter_Month,
to_char(sysdate,'YYYY-MM') Year_Month

from dual

pleaseeeeeeeeeeeee help me out


Re: time dimension loading week startign from saturday [message #218040 is a reply to message #218010] Tue, 06 February 2007 09:19 Go to previous message
Messages: 194
Registered: January 2006
Senior Member
Try this out. This gets the next 1000 days starting from the earliest coming Saturday..

trunc(v.mydate+b.lv) the_date,
v.mydate+b.lv date_time,
to_char(v.mydate+b.lv,'YYYY') Year,
to_char(v.mydate+b.lv,'Year') Year_spelled,
to_char(v.mydate+b.lv,'YY') short_year,
to_char(v.mydate+b.lv,'YYYY-Q') Year_quarter,
to_char(v.mydate+b.lv,'YYYY-Q') quarter,
to_char(v.mydate+b.lv,'MM') Month,
to_char(v.mydate+b.lv,'Month') Month_spelled,
to_char(v.mydate+b.lv,'YYYY-Q-MM') Year_quarter_Month,
to_char(v.mydate+b.lv,'YYYY-MM') Year_Month
from (select decode(to_char(sysdate,'DY'),'SAT',sysdate,next_day(sysdate,'SAT'))-1 mydate from dual) v, 
       (select level lv from dual connect by level < 1001) b

You may like to rewrite it to an optimal one.

[Updated on: Tue, 06 February 2007 09:27]

Report message to a moderator

Previous Topic: Playing With IP (Merged)
Next Topic: Checking for null in Nested table column
Goto Forum:

Current Time: Mon Feb 20 07:52:08 CST 2017

Total time taken to generate the page: 0.09426 seconds