ORA-01840: input value not long enough for date format

[ck388]

I'm trying to perform a select query on an oracle database but I get
this error.

ORA-01840: input value not long enough for date format

My query is:

SELECT * FROM TIGER.VIEW_TNW_MAINSTREAM WHERE
TO_CHAR(SYSDATE,'yyyy/mm/dd') =
TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd')

Does anyone know what's wrong?

When I do:

SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM TIGER.VIEW_TNW_MAINSTREAM

and

SELECT TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd') FROM
TIGER.VIEW_TNW_MAINSTREAM

i get no errors.

Would it be the fact that I have NULL values in the DUE_DATE field?

Thanks in advance for any help.

Tim :-)

[Jasper Scholten]

Tim,

If you write select statements as you did here, you will get into trouble
with your code in the near future.

Anyway, try:

SELECT *
FROM tiger.view_tnw_mainstream
WHERE trunc(due_date-5) = trunc(sysdate)

It is expected that column DUE_DATE is a date column.

HTH,
--
Jasper Scholten
DBA / Application Manager / Systems Engineer

Post by ck388
I'm trying to perform a select query on an oracle database but I get
this error.
ORA-01840: input value not long enough for date format
SELECT * FROM TIGER.VIEW_TNW_MAINSTREAM WHERE
TO_CHAR(SYSDATE,'yyyy/mm/dd') =
TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd')
Does anyone know what's wrong?
SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM TIGER.VIEW_TNW_MAINSTREAM
and
SELECT TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd') FROM
TIGER.VIEW_TNW_MAINSTREAM
i get no errors.
Would it be the fact that I have NULL values in the DUE_DATE field?
Thanks in advance for any help.
Tim :-)