Oracle Script to "build" a timeofday dimension(by minute)

View previous topic View next topic Go down

Oracle Script to "build" a timeofday dimension(by minute)

Post  developerpete on Thu Dec 20, 2012 3:42 pm

I couldn't find exactly what i was looking for, so I put this script together for creating the rows for a time of day dimension.. Hope this helps someone.

select 1 key
, 'No Time Specified' standard_time_desc
, 'No Time Specified' military_time_desc
, 'No Time Specified' am_or_pm_desc
, 'No Time Specified' Regular_Hours_Indicator
from dual
union
select n+1 key
, to_char(trunc(sysdate) + ((n-1)/1440),'HH:MI') standard_time_desc
, to_char(trunc(sysdate) + ((n-1)/1440),'HH24:MI') military_time_desc
, to_char(trunc(sysdate) + ((n-1)/1440),'AM') am_or_pm_desc
, case when to_char(trunc(sysdate) + ((n-1)/1440),'HH24') < '08' then 'Before Regular Business Hours'
when to_char(trunc(sysdate) + ((n-1)/1440),'HH24:MI') > '17:00' then 'After Regular Business Hours'
else 'During Regular Business Hours' end Regular_Hours_Indicator
from (
SELECT LEVEL N, 1 just_a_column FROM dual CONNECT BY LEVEL <= 1440)


developerpete

Posts : 5
Join date : 2012-11-05

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum