populating a date dimension between a range of two dates in PL/SQL

Question

I'm very new to plsql and am having a bit of trouble with one of my assignments. I've been asked to create a date dimension table and populate it with various date information.

I created my table as follows

CREATE TABLE date_dimension_table
(  
v_date DATE NOT NULL,
full_date VARCHAR2(50) NOT NULL,
day_of_week NUMBER(1,0) NOT NULL,
day_in_month NUMBER(2,0) NOT NULL,
day_in_year NUMBER(3,0) NOT NULL,
last_day_of_week_flag VARCHAR2(1) NOT NULL,
last_day_of_month_flag VARCHAR2(1) NOT NULL,
end_of_week_date DATE NOT NULL,
month_number NUMBER(2,0) NOT NULL,
month_name VARCHAR2(32) NOT NULL,
year_month CHAR(32) NOT NULL,
quarter_number NUMBER(1,0) NOT NULL,
year_and_quarter VARCHAR2(32) NOT NULL,
v_year NUMBER(4,0) NOT NULL,
CONSTRAINT date_dimension_table_PK PRIMARY KEY (v_date)
);

I then created the following procedure to populate the table.

create or replace PROCEDURE sp_populate_dates(
v_first_date_in DATE,
v_second_date_in DATE)

AS
--Declare two variables as DATE datatypes
v_current_date DATE;
v_end_date     DATE;

BEGIN

--Assign the start date and end date to the variables
v_current_date := TO_DATE(v_first_date_in, 'DDMMYYYY');
v_end_date := TO_DATE(v_second_date_in, 'DDMMYYYY');


--Delete whats currently in the table
DELETE FROM date_dimension_table;

--condition checks if the first date is before the second date
WHILE v_current_date <= v_end_date
LOOP
--insert into table
INSERT INTO date_dimension_table
(
    v_date,
    full_date,
    day_of_week,
    day_in_month,
    day_in_year,
    last_day_of_week_flag,
    last_day_of_month_flag,
    end_of_week_date,
    month_number,
    month_name,
    year_month,
    quarter_number,
    year_and_quarter,
    v_year       
)    
VALUES
(
    v_current_date,                                 --date
    TO_CHAR(v_current_date, 'Day, Month DD, YYYY'), --full date
    TO_NUMBER(TO_CHAR(v_current_date, 'D')) -1,     --day in week
    TO_CHAR(v_current_date,'DD'),                   --day in month        
    TO_CHAR(v_current_date,'DDD'),                  --day in year
    CASE                                            --last day of week flag
        WHEN TO_CHAR(v_current_date,'FMDay') = 'Sunday' THEN 'Y'
        ELSE 'N'
    END,
    CASE                                            --last day of month flag
        WHEN last_day(TO_DATE(v_current_date, 'MM/DD/YYYY')) = TO_DATE(v_current_date, 'MM/DD/YYYY') THEN 'Y'
        ELSE 'N'
    END,
    CASE                                            --date of next sunday
        WHEN TO_CHAR(v_current_date,'FMDay') = 'Sunday' THEN v_current_date
        ELSE NEXT_DAY(v_current_date,'SUNDAY')
    END,
    TO_CHAR(v_current_date,'MM'),                   --month number
    TO_CHAR(v_current_date,'MONTH'),                --name of month
    TO_CHAR(v_current_date,'MONTH YYYY'),           --month and year        
    TO_CHAR(v_current_date,'Q'),                    --number of quarter
    TO_CHAR(v_current_date,'YYYY Q'),               --year and quarter
    TO_CHAR(v_current_date,'YYYY')                  --year    

);
--Increment and assign the current date value to be re-evaluated
v_current_date := v_current_date + 1;

END LOOP;
END;

As the program has to insert information from a range of dates, I was using two dates as the input parameters and it was throwing up an error.

Example:

BEGIN
sp_populate_dates(01/01/2005, 01/01/2006);
END;

I was then getting errors suggesting it was the wrong type of argument to call in the function. I was wondering if anyone could help with this please, and show me where I'm going wrong. Cheers

Answer 1

You can populate such a table (for the dates between 2000-2030) by using the following query:

CREATE TABLE DIM_DATE
as
(
 select
  TO_NUMBER(TO_CHAR(DATE_D,'j')) as DATE_J,
  DATE_D,
  TO_CHAR(DATE_D,'YYYY-MM-DD') as DATE_V,
  TO_NUMBER(TO_CHAR(DATE_D,'YYYY')) as YEAR_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'Q')) as QUARTER_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'MM')) as MONTH_NUM,
  TRIM(TO_CHAR(DATE_D,'Month','nls_date_language=english')) as MONTH_DESC,
  TO_NUMBER(TO_CHAR(DATE_D,'IW')) as ISO_WEEK_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'IYYY')) as ISO_YEAR_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'DD')) as DAY_OF_MONTH_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'D')) as DAY_OF_WEEK_NUM,
  TRIM(TO_CHAR(DATE_D,'Day','nls_date_language=english')) as DAY_OF_WEEK_DESC,
  (CASE WHEN TRIM(TO_CHAR(DATE_D,'Day','nls_date_language=english')) IN ('Saturday','Sunday') THEN 'Weekend' ELSE 'Weekday' END) as DAY_TYPE_DESC
 from
 (
 select
  to_date('1999-12-31','YYYY-MM-DD')+ROWNUM as DATE_D
 from
  dual
  connect by level <= to_date('2029-12-31','YYYY-MM-DD')-to_date('1999-12-31','YYYY-MM-DD') 
 )
);

Answer 2

If you don't really need any date formatting and parsing, always use the SQL standard DATE literal:

BEGIN
  sp_populate_dates(DATE '2005-01-01', DATE '2006-01-01');
END;

Answer 3

First of all, you have procedure that get

v_first_date_in DATE,
v_second_date_in DATE

DATEs, but you're trying to pass even not a string. Try something like this

BEGIN
sp_populate_dates(TO_DATE('01/01/2005', 'dd/mm/yyyy'), TO_DATE('01/01/2005', 'dd/mm/yyyy'));
END;

another thing is - you process these parameters in the procedure, so you probably wanted to pass varchar2 type variables. That means,

create or replace PROCEDURE sp_populate_dates(
v_first_date_in IN VARCHAR2,
v_second_date_in IN VARCHAR2)
...

and call it like

BEGIN
sp_populate_dates('01/01/2005','01/01/2005');
END;

However be careful here: if type of date here is ('dd/mm/yyyy'), your processing date in the procedure ('ddmmyyyy') is not correct.

Hope it will help!