ORA-01830 On Delete but not on Select?

From: Jens Mohrmann <jmohrmann_at_snafu.de>
Date: Thu, 22 Mar 2007 22:00:00 +0100
Message-ID: <56g92gF29agrsU1@mid.uni-berlin.de>

Hello everyone,

I wonder if someone can shed some light on this. I have a table where the date is stored as a varchar2. The column is called datetime.

If I want to select all records more than 30 days ago with the following SQL, it works with no problem:

SELECT * FROM MFLOGBOOK WHERE TO_DATE(TRIM(DATETIME), 'DD.MM.YYYY HH24:MI:SS')+30 < TO_DATE('22.03.2007', 'DD.MM.YYYY')

However, if I want to delete them with

DELETE FROM MFLOGBOOK WHERE TO_DATE(TRIM(DATETIME), 'DD.MM.YYYY HH24:MI:SS')+30 < TO_DATE('22.03.2007', 'DD.MM.YYYY')

I get a

ORA-01830: date format picture ends before converting entire input string

The Database is Oracle 10g (10.2.0.1)

Any help is being appreciated

Jens Mohrmann Received on Thu Mar 22 2007 - 16:00:00 CDT