CODEWITHSUNDEEP
oracle-database
Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

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

I having an query like this 

SELECT test_VALUE FROM testTable
WHERE TO_DATE("test", 'yyyy-mm-dd') >= (TO_DATE('2012-01-01', 'yyyy-mm-dd') + '30'

When executing this query I getting the original data. But when I replacing 30 with another inner select query like this

select test_value from testtable
where TO_DATE("test", 'yyyy-mm-dd') >= (TO_DATE('2012-01-01', 'yyyy-mm-dd') + 
(select test_Value1 from testtable where some_condition=1)

I getting the error ORA-01840: input value not long enough for date format

The inner query select test_Value1 from testtable where some_condition=1 alone will output a result 30

Upvotes: 2

Views: 109131

Answers (6)

kiviko
kiviko

Reputation: 329

I usually using this format

AND TO_DATE(PAYMENTDATE, 'YYYYMMDD') = TRUNC(SYSDATE)

Upvotes: 1

Marco
Marco

Reputation: 2922

Just use COALESCE.

COALESCE(column, 0) to have 0 where your column IS NULL.

Upvotes: 1

mob43059
mob43059

Reputation: 41

I got this error when the datatype expected was a datetime and there was NULL in the column instead. The quick fix for me was to filter NULL out from the column with expected datetime values with a simple IS NOT NULL.

So you should be sure that your input query is not returning a NULL

Upvotes: 2

vapcguy
vapcguy

Reputation: 7547

If your query works when ran directly in say, Oracle SQL Developer, with 30, did you run select test_Value1 from testtable where some_condition=1 and get 30? Update test_Value1 to 30 where some_condition is equal to 1, so you get 30 back, and re-run the query.

Seems like you also have a typo - a missing closing parenthesis:

>= (TO_DATE('2012-01-01', 'yyyy-mm-dd') + 
(select test_Value1 from testtable where some_condition=1))

The error given could happen if the date comes up as an empty string. It's possible not being able to add the value from the final query, because of the missing closing parenthesis, could have made the value empty.

Also, not sure, but I don't think you want single quotes around 30 when adding it directly. You said you got the original list, which makes it sound like it didn't give you items that were 30 days old or older, like it looks like you want.

Upvotes: 1

Hemardath
Hemardath

Reputation: 31

I also got the same error. I got the RCA for my issue like one of the records has different values that caused this issue. Make sure that all the data in the column has proper data.

Upvotes: 3

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

You made query with implicit datatype conversions, so you shouldn't be surprised by some strange behavior. It means that your expression produces a result other that DATE and Oracle tries to convert it to date because it required for comparision.

Be more exact, say,

select test_value from testtable
where TO_DATE("test", 'yyyy-mm-dd') >= DATE '2012-01-01' + 
(select to_number(test_Value1) from testtable where some_condition=1)

and you'll either succeed or got a more descriptive error message. Btw, your SQL means then testtable."test" isn't DATE itself - why?

Upvotes: 0

Related Questions