~Note~

Please note that you can always click on an image in my postings and it will render a clear full sized version in a separate browser page! Also please note that this blog is best viewed with Firefox and Chrome
Google
 

Friday, February 5, 2010

Date Dimensions anybody?

Here is some sample code for creating a date dimension, It's been out there before, My colleague Dave posted this on his blog at one point. I'd like to take a moment and maybe look at how Oracle implements their date dim with the OWB date dimension wizard....hmmmm!

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
dt date_value,
TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
TO_CHAR (dt, 'Mon DD, YYYY') date_name,
TO_CHAR (dt, 'YYYY') year_num,
TRUNC (dt, 'YYYY') year_start_date,
ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
'Q' || TO_CHAR (dt, 'Q') quarter_name,
TRUNC (dt, 'Q') quarter_start_date,
ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
TO_CHAR (dt, 'MM') month_num,
TO_CHAR (dt, 'Mon') month_short_name,
TO_CHAR (dt, 'Month') month_long_name,
TRUNC (dt, 'MM') month_start_date,
LAST_DAY (dt) month_end_date,
TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
TO_CHAR (dt, 'WW') week_of_year_num,
TO_CHAR (dt, 'W') week_of_month_num,
TRUNC (dt, 'W') week_start_date,
TRUNC (dt, 'W') + 6 week_end_date,
TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
TO_CHAR (dt, 'D') weekday_num,
TO_CHAR (dt, 'Day') weekday_name
FROM (SELECT TO_DATE (:start_date) + ROWNUM dt
FROM DUAL
CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

No comments:

Post a Comment