Invalid Date format Error

I'm trying to execute the query
select to_date(to_char(completed_date,'YYYYMMDD')
       || completed_time,'YYYYMMDDHH24:MI') from change_order

and I'm getting the error :

Ora 1840 Input value not long enough for date format.

I'm executing in a database in which full database import was done from another database.Now,this query was working in the original database.
Is there any parameter setting that I should give for this query to work?.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

According to the date format you have used, you are getting the date from "completed_date" in "YYYYMMDD" format and "completed_time" in "HH24:MI" format right ?

if yes , then make sure of that? probably your "completed_time" might also be containing the date in it.

post the values of completed_date and completed_time ?

Sorry now checked your statement.
Try this.

select to_date( to_char(completed_date,'YYYYMMDD')||to_char(completed_time,'HH24:MI'), 'YYYYMMDDHH24MI') from change_order;

to_char function for completed_time was missing .

I agree with sudhi. Please post the some of the records in that  table.
The following query gives you all bad rows.

select completed_date, completed_time from change_order where completed_date is null or completed_timeis null or length( completed_time) != 8

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lvenkatAuthor Commented:
Hi Hauke,

 Thanks for your suggestion. Your query helped to fetch bad records. I eliminiated the bad records and now my query works.

 Thanks for others who suggested.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.