Thinking Out Loud

May 7, 2011

Create Date Dimension from SQL

Filed under: Uncategorized — mdinh @ 2:05 pm

Note to self.

SELECT 
  TO_NUMBER(TO_CHAR(dt,'YYYYMMDD')) date_key,
  dt date_value,
  TO_NUMBER(TO_CHAR(dt,'DD')) day,
  TO_NUMBER(TO_CHAR(dt,'MM')) month,
  TO_NUMBER(TO_CHAR(dt,'Q')) quarter,
  TO_NUMBER(TO_CHAR(dt,'YYYY')) year,
  TO_CHAR(dt,'DAY') AS day_name,
  TO_CHAR(dt,'MONTH') AS month_name,
  TO_NUMBER(TO_CHAR(dt,'D')) day_week,
  TO_NUMBER(TO_CHAR(dt,'DDD')) day_year,
  TO_NUMBER(TO_CHAR(dt+1,'W')) AS week_month,
  TO_NUMBER(TO_CHAR(dt+1,'WW')) AS week_year,
  TO_NUMBER(TO_CHAR(dt+1,'IW')) AS iso_week_year		 
FROM (  
  SELECT LEVEL n, TO_DATE('31/12/2010','DD/MM/YYYY')+NUMTODSINTERVAL(LEVEL,'day') dt
  FROM DUAL
  CONNECT BY LEVEL <= 365
)
ORDER BY 1 ASC;

Reference: Oracle Date and Time formats

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: