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...
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
6 Answers
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.
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
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?
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.
I usually using this format
AND TO_DATE(PAYMENTDATE, 'YYYYMMDD') = TRUNC(SYSDATE)
Just use COALESCE.
COALESCE(column, 0) to have 0 where your column IS NULL.
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
