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

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
CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

No comments:

Post a Comment