user1425503 user1425503 - 1 month ago 11
SQL Question

sysdate-1 not working in Oracle Jobs

I have a stored procedure in Oracle. I want to call it in JOBS of Oracle.

DECLARE
P_DATE DATE;
BEGIN

P_DATE := TO_DATE ('19/10/2016', 'DD/MM/YYYY');

MITRA.PENJUALAN_ANTAR_CABANG.REPORTKONSOLRK ( P_DATE );
COMMIT;
END;


the result is right when I execute P_DATE as '19/10/2016'. But when I change it like this below the result is not display.

DECLARE
P_DATE DATE;
BEGIN
P_DATE := TO_DATE (TRUNC (SYSDATE - 1), 'DD/MM/YYYY');

MITRA.PENJUALAN_ANTAR_CABANG.REPORTKONSOLRK (P_DATE);
COMMIT;
END;


What is my query mistake, while if I run
select trunc(SYSDATE-1) from dual
the date was right.

Answer

SYSDATE - 1 is already a date. Passing it to to_date will produce a syntax error. Just drop the to_date call and you should be fine:

P_DATE := TRUNC(SYSDATE - 1);
Comments