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
"ck388" <go****@timyee.com> schreef in bericht
news:69**************************@posting.google.c om...

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 :-)