Problem with Trunc(Dates)
637614Jul 29 2009 — edited Jul 29 2009Hey guys,
I need your help with a trunc statement.
I have a table SHTTRAN with 3 columns:
SHTTRAN_ID SHTTRAN_SEQ_NO SHTTRAN_REQUEST_DATE
901325 2 7/20/2008 6:11:28 PM
900678 4 7/20/2008
The request date column has few records with timestamp and few without timestamp as shown above.
I have a procedure within a package that deletes one record from this table as follows:
exec deletepackage.p_delete_szptpsr('901325', 2, '20-JUL-2008');
exec deletepackage.p_delete_szptpsr('900678', 4, '20-JUL-2008');
I have a truc(shttran_request_date) inside the procedure to remove the timestamp as shown below:
CREATE OR REPLACE PACKAGE BODY "DELETEPACKAGE" IS
PROCEDURE p_delete_szptpsr (p_shttran_id IN saturn.shttran.shttran_id%TYPE,
p_shttran_seq_no IN saturn.shttran.shttran_seq_no%TYPE,
p_shttran_request_date IN saturn.shttran.shttran_request_date%TYPE) IS
BEGIN
DELETE FROM saturn.shttran
WHERE shttran.shttran_id = p_shttran_id
and shttran.shttran_seq_no = p_shttran_seq_no
and trunc(shttran_request_date) = p_shttran_request_date;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted the Request No (' || p_shttran_seq_no || ') of the Student (' || p_shttran_id ||') for the requested date of (' || p_shttran_request_date ||')');
END p_delete_procedure;
END DELETEPACKAGE;
/
Though I execute the procedure it is not deleting the record with the timestamp. Can someone help me with this?
Thank you so much in advance.