ORA-01840: input value not long enough for date format
938166May 18 2012 — edited May 18 2012i am using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Query is:
SELECT rep_records.*
FROM (SELECT decode(mrs.frequency,
'HOUR',
(mrs.last_run + (mrs.hour_interval / 24)),
'DAY',
to_date((mrs.last_run +
(nvl(mrs.hour_interval, '24') / 24)) || ' ' ||
decode(mrs.start_time,
'00',
'00:00:01',
mrs.start_time),
'DD-MON-RRRR HH24:MI:SS')) AS NEXT_RUN,
mrs.last_run,
mrs.frequency,
mrs.start_time,
mrs.hour_interval,
mrs.min_interval,
rsh.report_type
FROM mail_report_lines mrl,
report_setup_head rsh,
mail_report_schedule mrs,
mail_report_email mre
WHERE mrl.report_id = rsh.report_id
AND mrs.report_id = mrl.report_id
AND mrs.line_id = mrl.line_id
AND mrs.line_id = mre.line_id
AND rsh.report_type in ('N', 'H')
ORDER BY mrs.report_id) rep_records
where rep_records.next_run < sysdate;
the above query giving me the error on last where clause (where rep_records.next_run < sysdate;) error is (ORA-01840: input value not long enough for date format).