Oracle/SQL: invalid number format model when concatenating a date and a time into a single date value

Question

I am trying to create a trigger in Oracle that compares two dates and then deletes records if the difference of the two dates falls below a certain value. I have a full date value with the format 'DD-MON-YYYY HH24MI' and then for the second date value, I want to concatenate a 'DD-MON-YYYY' value with a 'HH24MI' value.

The problem I am getting is that when I try to concat the date and time value together using to_char, and then using to_date on that returned value, it gives me a ORA-01481 invalid number format error. The relevant lines from the trigger itself are below. If anyone can help me out with this, it would be greatly appreciated!

CREATE OR REPLACE TRIGGER dateTrig
...
DECLARE
    day date;
    ftime date;
    CURSOR c_table1 IS SELECT ...;
BEGIN
FOR entry IN c_table1 LOOP
    day := to_date(entry.fdate);
    ftime := to_date(to_char(day, 'DD-MON-YYYY') || ' ' || to_char(entry.dtime, 'HH24MI'), 'DD-MON-YYYY HH24MI'); -- this is the line that is causing the error
    dbms_output.put_line(day || ', ' || ftime);
END LOOP;
END;
/

What's the data type of the DTIME column? Your code assumes it's a date. But I doubt it is. — Codo
DTIME is a varchar(4) that looks like '1600' for 4:00pm, or '1530' for 3:30pm, etc. — jsutton

Codo Codo · Accepted Answer · 2011-12-03T19:21:49

Since DTIME is not a date type, you cannot use TO_CHAR with a date format. If it's zero padded to a length of 4 characters, you can simplify it like this:

ftime := to_date(to_char(day, 'DD-MON-YYYY') || ' ' || entry.dtime, 'DD-MON-YYYY HH24MI');

Oracle/SQL: invalid number format model when concatenating a date and a time into a single date value

2 Answers