topic Re: ORA-01840: input value not long enough for date format in SAS Programming

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

ashwini2 — Fri, 03 Aug 2018 15:30:35 GMT

case when status not in ('Cancelled', 'Closed', 'Filled' ) then sysdate
when date_filled < date_req_approved then date_last_status_change else coalesce(date_filled, date_last_status_change)
end as date_filled

I think the issue is with sysdate.

ERROR: CLI cursor fetch error: [Oracle][ODBC][Ora]ORA-01840: input value not long enough for date format

Any suggestions how to make this work?

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

kiranv_ — Fri, 03 Aug 2018 15:52:27 GMT

Are you running pass through. This is more of oracle question but one thing is for sure. columns you are comparing or in coalesce do not have same formats

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

ashwini2 — Fri, 03 Aug 2018 16:00:43 GMT

I am running a passthrough. Weirdly my code works just fine in Oracle SQL.

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

kiranv_ — Fri, 03 Aug 2018 16:11:34 GMT

Are you running an explicit sql pass through(via connect statement) or an implicit sql

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

Kurt_Bremser — Fri, 03 Aug 2018 16:32:09 GMT

How is columns sysdate populated? Does it have the same attributes as date_filled, date_req_approved, date_last_status_change?

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

ashwini2 — Fri, 03 Aug 2018 16:36:13 GMT

I am using the connect statement

proc sql;
connect to odbc as ods

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

kiranv_ — Fri, 03 Aug 2018 22:22:46 GMT

if it has worked in oracle sql developer and not working in connect statement. This might be any issue with ODBC connection related issue.

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

Patrick — Sat, 04 Aug 2018 05:40:24 GMT

@kiranv_

Just Googling the error Oracle throws: It could be that the data types of the columns you're using are not all of type DATE and that some implicit conversion is happening. If such implicit conversion is going on then Oracle will use the format as set in NLS_DATE_FORMAT. If this format doesn't match the string it's trying to convert then you could get the Oracle error you observing.

Following this thought: It's possible that NLS_DATE_FORMAT has a different value depending on how you connect to Oracle (via SAS or SQL Developer).

What I would do:

1. Check if all your data types are DATE

2. If not all columns of type DATE: Cast strings to date using an explicit format using the to_date() function. Don't rely on implicit conversions.

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

SuryaKiran — Mon, 06 Aug 2018 15:24:34 GMT

Make sure that all these variable(sysdate, date_last_status_change, date_filled, date_last_status_change) have same date or datetime formats.

If your sysdate is 'DD/MM/YYYY 00:00:00' format and date_last_status_change is 'DD/MM/YYYY' format, then you might get this possible error. Convert them to same format using TO_DATE() in Oracle or convert them all to characters using TO_CHAR.