Error Date format Picture Ends Before Converting Entire Input String

0
votes

In Oracle I'm getting the error:

Error Date format Picture Ends Before Converting Entire Input String

when I'm running the following query:

SELECT *
FROM TABLE
WHERE (trunc(sysdate) - to_date (completion_date,'YYYY-MM-DD'))>0

The completion_date field is in varchar and its value is 2018-11-30 00:00:00.723000

3
sqloracleplsql
Why on earth do you store date/time values as string? – Wernfried Domscheit

3 Answers

1
votes

You need to take the first 10 characters of the string and proceed as follows:

to_Date(SUBSTR(completion_date,1,10),'YYYY-MM-DD')
1
votes

The value stored as varchar is a TIMESTAMP, not a DATE.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss' ;
Session altered 

SQL>  select to_timestamp('2018-11-30 00:00:00.723000','YYYY-MM-DD HH24:MI:SS.FF') from dual;

TO_TIMESTAMP('2018-11-3000:00:00.723000','YYYY-MM-DDHH24:MI:SS.FF')
---------------------------------------------------------------------------
30-NOV-18 12.00.00.723000000 AM

If you want a date

SQL> select to_date(to_char(to_timestamp('2018-11-30 00:00:00.723000','YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS')) from dual;

TO_DATE(TO_CHAR(TO_
-------------------
2018-11-30 00:00:00

SQL>
0
votes

please use below statement select * from TABLE where (trunc(sysdate) - to_date(substr(completion_date,1,10),'YYYY-MM-DD'))>0