-1

I am trying to execute a query looking something like this:

create table A as    

select 
    userid, to_date(date1, 'mm/dd/yyyy') as startDate, 
    to_date(date2, 'mm/dd/yyyy') as endDate
from TABLE;

I am getting the error:

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

What's really strange here is that when I run only the SELECT... part of the query it works perfectly, I am only getting the error when I try to create a table. I absolutely need to make a table, so how can I get around this?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mjavon
  • 237
  • 1
  • 6
  • 14
  • 1
    Are `date1` and `date2` date columns? If so, why would you call `to_date` on a date? That doesn't make sense. If you want to set the time component of `date1` and `date2` to midnight when you copy the data to `A`, you can use `trunc(date1)` – Justin Cave Aug 11 '15 at 15:14
  • you'll need to provide more information than this .. like the specs for "TABLE" .. and some sample data which is triggering the error ... so far you've only provided a partial question .. and the important information you're keeping from us ;) – Ditto Aug 11 '15 at 15:14
  • What is the datatype of the date1 and date2 columns? – Boneist Aug 11 '15 at 15:14
  • Oracle to_date function converts a string to a date, so I would expect that date1 and date2 are varchar or nvarchar. Right? –  Aug 11 '15 at 15:20
  • @GiliusMaximus in an ideal world, yes. Yet some people seem to use this as a really strange and buggy way of doing a trunc(). – Boneist Aug 11 '15 at 15:21
  • Could you give some sample values from date1 and date2. My guess is that some of the values in those fields have extra stuff in them, like maybe time, or different dividers. As long as you run as a select, Oracle might be ok to just give you different results on those, maybe a null value or something else you did not notice was different, but as soon as you tried to use the results to create a table, the create statement choked on the differences. –  Aug 11 '15 at 15:22
  • date1 and date2 are both varchar columns. I think the issue is coming from a handful of the values which are datetimes, although I was under the impression that using to_date on datetimes would simply drop the time element. – mjavon Aug 11 '15 at 15:35
  • if some rows have time elements and some don't, you'll either have to strip off the time using substr() or add in a time of midnight to those rows which don't have it before doing the date conversion. Sounds like substr() would be the way to go in this case. E.g.: `to_date(substr(date1, 1, 10), 'mm/dd/yyyy')` – Boneist Aug 11 '15 at 16:18

1 Answers1

1

There's likely some bad data past the first few rows. When you run the select, it will do the conversion for the first few (less than 1000 for me) rows only. The results are paged. You'll need to clean up the data first. You could write a simple function like this to figure out which dates it's failing on.

How to handle to_date exceptions in a SELECT statment to ignore those rows?

Community
  • 1
  • 1
Mark
  • 46
  • 4
  • There are some bad data points, some of the dates are datetimes, but most are not. There are few enough datetimes in there that I was comfortable throwing them out by using WHERE LENGTH(date) <= 10 though. – mjavon Aug 11 '15 at 17:11