Re: Why is this not working

Thanks Jim,

I will try it that way. Thanks once again for answering my post. May the good Lord bless you.

J.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<kdDu9.122032$La5.390427_at_rwcrnsc52.ops.asp.att.net>...
> DATE :='01011996'
> isn't a date it is a string.
> to_date('01011996','mmddyyyy') is a date or
> to_date('01011996','ddmmyyyy') is a date(see the problem with not specifying
> a format)
> I think you want:
> CREATE OR REPLACE PROCEDURE TIME_PROC
> ( startdate in DATE default to_date('01011996','mmddyyyy'),
> enddate in DATE default to_date('12311998','mmddyyyy')...
> (also in your example you ar inputting numbers not dates)
>
> then
> exec TIME_PROC(to_date('01012002','mmddyyyy')
> to_date('09302002','mmddyyyy'))
>
> But then looking at your code you have defined incdate as a date and then
> you treat it as a string.
> Dates aren't strings. You can print a date as a string in a particular
> format and that is the concept that I think you are missing.
> Jim
>
> "Johnny" <kdankwah_at_aol.com> wrote in message
> news:732d872a.0210261021.5988b063_at_posting.google.com...
> > I am trying to do an insert into a table called time. Could someone
> > please tell me if this will do an insert. Also how will I do the
> > execute on this procedure. The arguments are startdate and enddate.
> > Help.
> >
> > CREATE OR REPLACE PROCEDURE TIME_PROC
> > ( startdate DATE :='01011996',
> > enddate DATE :='12311998')
> > AS
> > incdate DATE;
> > recno INTEGER;
> > BEGIN
> > incdate := startdate;
> > recno := 1;
> > WHILE incdate <= enddate LOOP
> > INSERT INTO time
> > (Time_key,
> > Year_key,
> > Year_id,
> > Year_desc,
> > Quarter_key,
> > Quarter_id,
> > Quarter_desc,
> > Month_key,
> > Month_id,
> > Month_desc,
> > Date_id,
> > Date_desc)
> > VALUES( recno,
> > recno,
> > TO_NUMBER( TO_DATE( incdate, 'YYYY' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'YYYY' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'Q1' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'Q1'||'-'||'YYYY' ) ),
> > TO_NUMBER(TO_DATE( incdate, 'Q1'||'-'||'YYYY' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'MM' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'MM'||'-'||'YY' ) ),
> > TO_NUMBER( TO_DATE( incdate, 'MM'||'-'||'YY' ) ),
> > TO_DATE( incdate, 'MMDDYYYY' ) ,
> > TO_DATE( incdate, 'MMDDYYYY' ) );
> > recno := recno + 1;
> > incdate := incdate + 1;
> > END LOOP;
> > COMMIT;
> > END time_proc;
> > /
> >
> > Execute time_proc TO_DATE(01012002,09302002)
> >
> > /
> >
> > Its not doing the insert.
> >
> >
> > Thanks,
Received on Sun Oct 27 2002 - 07:19:55 CST