Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01830: date format picture ends before converting entire input string

User_6IWBOJul 13 2021

I am using below query for External table linked with a csv file. I use TOAD tool and Oracle 10g Express DB.
select DIVISION,count(*) from NRTABLE where TO_DATE(substr(BILLDATE,1,10),'YYYY-MM-DD')>'23-Jun-2021' GROUP BY DIVISION
It gives an error : "ORA-01830: date format picture ends before converting entire input string"
But if I do not use count(*) aggregate function, my below query works fine.
select * from NRTABLE where TO_DATE(substr(BILLDATE,1,10),'YYYY-MM-DD')>'23-Jun-2021'
Kindly suggest the solution.
Note: External table has BILLDATE of VARCHAR2(250 CHAR) type and BILLDATE in csv file is in this (29-06-2021 4:57:18 AM) format.

Added on Jul 13 2021

#date, #general-database-discussions

6 comments

4,685 views