to store a date without the time component do trunc(theDate) Then the time
component is midnight.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Steven Markey" <s.markey_at_worldnet.att.net> wrote in message
news:KGjUa.71444$3o3.4803760_at_bgtnsc05-news.ops.worldnet.att.net...
> Hello:
>
> My table is described as:
>
> RENTAL# NUMBER
> CUSTNAME VARCHAR2(15)
> ITEM_NUMBER NUMBER(4)
> OUT_DATE DATE
> DUE_DATE DATE
> IN_DATE DATE
> FEE NUMBER(6,2)
>
> So, with that said, I've 1st set the following session level variable:
>
> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY hh24:mi';
>
> After this I wrote and sucessfully compiled this procedure:
>
> create or replace procedure AddRental (
> p_custname IN rental.custname%TYPE,
> p_item_number IN rental.item_number%TYPE,
> p_out_date IN rental.out_date%TYPE,
> p_fee IN rental.fee%TYPE) AS
>
> /*Defined all params above with type from table rental. Note the
> exclusion of in_date as this param
> is not necessary...going on the premise that the business won't know
> when the customer will
> actually return the rental.*/
>
> BEGIN
>
> INSERT INTO RENTAL (rental#, custname, item_number, out_date, due_date,
> in_date, fee)
> VALUES (RentalSeq.NEXTVAL, p_custname, p_item_number,
to_date(p_out_date,
> 'MM/DD/YYYY hh24:mi'),
> next_business_date(to_date(p_out_date, 'MM/DD/YYYY')), NULL, p_fee);
>
> DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(p_out_date,
> 'DAY') || ' ' || to_date(p_out_date, 'MM/DD/YYYY'));
>
> END AddRental;
>
> Where next_business_date is as such:
>
> create or replace function next_business_date (D IN DATE)
> RETURN DATE IS
>
> --Function takes a date and returns the next business day.
>
> --Declare variable to store result
> v_bizdate DATE;
>
> --Declare a variable (in CHAR format) to store passed value for string
> comparison
> v_storedate CHAR(3);
>
> --Begin body of function
> BEGIN
>
> --Convert and store passed value in CHAR format into v_storedate
> SELECT to_char(D, 'DY')
> INTO v_storedate
> FROM dual;
>
> --Take the date and check to see if it is a business day or day on
> the weekend.
>
> --Test for Friday and add 3 days if this is so.
> IF v_storedate = 'FRI' THEN
> v_bizdate := D + 3;
>
> --Test for Saturday and 2 days if this is so.
> ELSIF v_storedate = 'SAT' THEN
> v_bizdate := D + 2;
>
> --If it not Friday or Saturday add 1 day.
> ELSE
> v_bizdate := D + 1;
>
> END IF;
>
> --Return the next business day.
> RETURN v_bizdate;
>
> END next_business_date;
>
> And this is what I get when I execute the procedure:
>
> [CRC] SQL> exec AddRental('Clinton', 25, '04/03/2003 14:00', 3.50);
> BEGIN AddRental('Clinton', 25, '04/03/2003 14:00', 3.50); END;
>
> *
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input string
> ORA-06512: at "SCM24.ADDRENTAL", line 13
> ORA-06512: at line 1
>
> So, seeking a different way of performing this action I tried removing the
> to_date() functions with no luck. Here is that code:
>
> create or replace procedure AddRental (
> p_custname IN rental.custname%TYPE,
> p_item_number IN rental.item_number%TYPE,
> p_out_date IN rental.out_date%TYPE,
> p_fee IN rental.fee%TYPE) AS
>
> /*Defined all params above with type from table rental. Note the
> exclusion of in_date as this param
> is not necessary...going on the premise that the business won't know
> when the customer will
> actually return the rental.*/
>
> BEGIN
>
> INSERT INTO RENTAL (rental#, custname, item_number, out_date, due_date,
> in_date, fee)
> VALUES (RentalSeq.NEXTVAL, p_custname, p_item_number, p_out_date,
> next_business_date(p_out_date), NULL, p_fee);
>
> DBMS_OUTPUT.PUT_LINE('Transaction occured on: ' || to_char(p_out_date,
> 'DAY') || ' ' || to_date(p_out_date, 'MM/DD/YYYY'));
>
> END AddRental;
>
> After recompiling the above proc I tried passing the date (in numerous
> formats, such as the defualt Oralce format, 'DD-MMM-YYYY', as well as
> attempting a to_date call within the exec statement) for the out_date
param
> with no luck and the same error.
>
> Also, please note that I DO NOT want to store the due_date value as a date
&
> time attribute (I want just the date).
>
> So, any ideas?
>
>
>
Received on Fri Jul 25 2003 - 20:21:47 CDT
