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


This is another real-time mistake I faced today. I was parsing a log file and uploading into DB. One of the dates in the log file doesnt follow the date format. So I got this error. I cant give that code in this post, I am giving an example below.

SQL> select to_date('20051101 00:00:01', 'YYYYMMDD') from dual ;
select to_date('20051101 00:00:01', 'YYYYMMDD') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

I hope you have noticed the error. The query should be like this.

SQL> select to_date('20051101 00:00:01', 'YYYYMMDD hh24:mi:ss') from dual ;

TO_DATE('
---------
01-NOV-05

You can have a detailed view on the Oracle date formats here
http://www.ss64.com/orasyntax/fmt.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s