Karthik Karthik - 5 months ago 9
SQL Question

Invalid NUMBER Error in PLSQL block

I have the following query which gives invalid number exception. If i just replace the date (06/10/2016 11:53:46 AM) with sysdate, it works fine.please let me know where am i wrong in this.

DECLARE
count NUMBER;
BEGIN
SELECT COUNT(*) INTO count FROM TABLE_NAME WHERE ID =123
AND TO_CHAR('06/10/2016 11:53:46 AM','HH24:MI') > REJECTTIME;
DBMS_OUTPUT.PUT_LINE(rej_count);
END;

Answer

You are confused. sysdate is a date/time value, not a string. If you want equivalent code, then use:

TO_CHAR(TO_DATE('06/10/2016 11:53:46 AM','MM/DD/YYYY HH:MI:SS AM'), 'HH24:MI') > REJECTTIME

That is, convert the value to a date and then to the string for the comparison.

You are getting the error because your string is not in a format compatible with the 'HH24:MI' formatting code.