Every mature database needs a date/time table in order to properly manage dates and times. It is way better to have a preloaded table with all possible date formats you might possibly need. See (and feel free to use) the script below.
This script will generate details to the minute.
/*
CREATE TABLE d_date_time
PARTITION BY RANGE(date_time)
INTERVAL (NumToYmInterlal (1, 'MONTH') -- 43800 records (minutes) in a month
(
PARTITION p_init VALUES LESS THEN (DATE '3000-01-01')
)
AS
*/
SELECT time_key as date_time
, to_char(time_key, 'YYYY/MM/DD HH24:MI:SS') as time_key
, (time_key - to_date('01/01/1970', 'MM/DD/YYYY')) * 60 * 60 * 24 as unix_timestamp
-- HOURS
, to_char(time_key, 'HH24') as hour_24
, to_char(time_key, 'HH') as hour_12
, to_char(time_key, 'PM') as am_pm
, CASE
WHEN to_char(time_key, 'MI') BETWEEN 0 AND 29 THEN '1'
WHEN to_char(time_key, 'MI') BETWEEN 30 AND 59 THEN '2'
ELSE '0'
END as hour_half
, CASE
WHEN to_char(time_key, 'MI') BETWEEN 0 AND 29 THEN '0-29'
WHEN to_char(time_key, 'MI') BETWEEN 30 AND 59 THEN '30-59'
ELSE '0'
END as hour_half_range
, CASE
WHEN to_char(time_key, 'MI') BETWEEN 0 AND 14 THEN '1'
WHEN to_char(time_key, 'MI') BETWEEN 15 AND 29 THEN '2'
WHEN to_char(time_key, 'MI') BETWEEN 30 AND 44 THEN '3'
WHEN to_char(time_key, 'MI') BETWEEN 45 AND 59 THEN '4'
ELSE '0'
END as hour_quarter
, CASE
WHEN to_char(time_key, 'MI') BETWEEN 0 AND 14 THEN '0-14'
WHEN to_char(time_key, 'MI') BETWEEN 15 AND 29 THEN '15-29'
WHEN to_char(time_key, 'MI') BETWEEN 30 AND 44 THEN '30-44'
WHEN to_char(time_key, 'MI') BETWEEN 45 AND 59 THEN '45-59'
ELSE '0'
END as hour_quarter_range
-- MINUTES
, to_char(time_key, 'MI') as min
, CASE
WHEN to_char(time_key, 'SS') BETWEEN 0 AND 29 THEN '1'
WHEN to_char(time_key, 'SS') BETWEEN 30 AND 59 THEN '2'
ELSE '0'
END as min_half
, CASE
WHEN to_char(time_key, 'SS') BETWEEN 0 AND 29 THEN '0-29'
WHEN to_char(time_key, 'SS') BETWEEN 30 AND 59 THEN '30-59'
ELSE '0'
END as min_half_range
-- DAYS
, trunc(time_key) as day
, to_char(time_key, 'YYYY/MM/DD') as date_key
, to_char(time_key, 'DD') as daynum
, to_char(time_key-1, 'D') as daynum_in_week
, to_char(time_key, 'DDD') as daynum_in_year
, to_char(time_key, 'DAY') as day_name
-- WEEKS
, CASE
WHEN to_char(time_key-1, 'D') IN (6,7) THEN 1
ELSE 0
END as is_weekend
, to_char(time_key, 'iW') as week_number
, next_day(time_key-7, 'MONDAY') as week_start
, next_day(time_key-1, 'SUNDAY') as week_end
, to_char(next_day(time_key-7, 'MONDAY'), 'MON DD') || ' - ' || to_char(next_day(time_key-1, 'SUNDAY'), 'MON DD') as week_range
-- MONTHS
, to_char(time_key, 'YYYY/MM') as year_month
, to_char(time_key, 'MM') as month_number
, to_char(time_key, 'MON') as month_name
-- QUARTERS
, CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN '1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN '2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN '3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN '4'
ELSE 'N/A'
END as quarter
, CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
ELSE 'N/A'
END as quarter_str
, to_char(time_key, 'YYYY') || '/' || CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
ELSE 'N/A'
END as year_quarter
-- YEAR
, to_char(time_key, 'YYYY') as year
, DECODE(to_char(last_day(to_date( '01-FEB-'|| to_char(time_key, 'YYYY'))), 'DD'),'29', 1,0) as leap_year_flag
FROM (
SELECT to_date('10/1/2013 00:00:00', -- start datetime
'MM/DD/YYYY HH24:MI:SS')-1/24/60 + level/24/60 as time_key
FROM dual
CONNECT BY level <= 1440 -- minute interval
)
WHERE 1=1
;
Or just a simple day table
--CREATE TABLE d_date AS
SELECT -- DAYS
trunc(time_key) as day
, to_char(time_key, 'YYYY/MM/DD') as date_key
, to_char(time_key, 'DD') as daynum
, to_char(time_key-1, 'D') as daynum_in_week
, to_char(time_key, 'DDD') as daynum_in_year
, to_char(time_key, 'DAY') as day_name
-- WEEKS
, CASE
WHEN to_char(time_key-1, 'D') IN (6,7) THEN 1
ELSE 0
END as is_weekend
, to_char(time_key, 'iW') as week_number
, next_day(time_key-7, 'MONDAY') as week_start
, next_day(time_key-1, 'SUNDAY') as week_end
, to_char(next_day(time_key-7, 'MONDAY'), 'MON DD') || ' - ' || to_char(next_day(time_key-1, 'SUNDAY'), 'MON DD') as week_range
-- MONTHS
, to_char(time_key, 'YYYY/MM') as year_month
, to_char(time_key, 'MM') as month_number
, to_char(time_key, 'MON') as month_name
-- QUARTERS
, CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN '1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN '2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN '3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN '4'
ELSE 'N/A'
END as quarter
, CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
ELSE 'N/A'
END as quarter_str
, to_char(time_key, 'YYYY') || '/' || CASE
WHEN to_char(time_key, 'MM') IN (1,2,3) THEN 'Q1'
WHEN to_char(time_key, 'MM') IN (4,5,6) THEN 'Q2'
WHEN to_char(time_key, 'MM') IN (7,8,9) THEN 'Q3'
WHEN to_char(time_key, 'MM') IN (10,11,12) THEN 'Q4'
ELSE 'N/A'
END as year_quarter
-- YEAR
, to_char(time_key, 'YYYY') as year
, DECODE(to_char(last_day(to_date( '01-FEB-'|| to_char(time_key, 'YYYY'))), 'DD'),'29', 1, 0) as leap_year_flag
FROM (
SELECT to_date('10/1/2013', -- start date
'MM/DD/YYYY') + level - 1 as time_key
FROM dual
CONNECT BY level <= 200 -- number of days
)
WHERE 1=1
;
Instead of the number of days (so you don’t need to compute it), you can use a date difference between two days.