OraFAQ Forum: SQL & PL/SQL » ORA-01830: date format picture ends before converting entire input string

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830: date format picture ends before converting entire input string (oracle 10g Windowxp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01830: date format picture ends before converting entire input string [message #513759]

Wed, 29 June 2011 05:26

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
  2  values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
  3  ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
                     *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

Table Structure is

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------

 ANI                                                VARCHAR2(10)
 DATE_TIME                                          DATE
 DURATIONS                                          VARCHAR2(10)
 SRC                                                VARCHAR2(10)
 CIRCLEID                                           VARCHAR2(10)

SQL>

Kindly help me in this

Regards
Pradeep

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513760 is a reply to message #513759]

Wed, 29 June 2011 05:33

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use to_Date with the appropriate format mask. Always.

by the way - your date looks like a timestamp. Dates don't have fractional seconds.

[Updated on: Wed, 29 June 2011 05:34]

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513763 is a reply to message #513759]

Wed, 29 June 2011 05:43

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have almost 150 posts and posted many many questions and you still don't know that '29-JUN-11 03.19.41.000000000 PM' is a STRING not a DATE?

ALWAYS always always use TO_DATE when you want to convert to a date during INSERT or UPDATE. There is no exception. Otherwise Oracle will do it for you and not how you want to do it (thus the error you have).

Regards
Michel

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513769 is a reply to message #513763]

Wed, 29 June 2011 06:05

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

When I am using the To_date() function, I am getting the same below error


SQL> l
  1  insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
  2* values ('9569333585',to_date('29-JUN-11 03.19.41.000000000 PM','DD-MON-YY H
H24:MI:SS'),'1027','51010','BIR')
SQL> /
values ('9569333585',to_date('29-JUN-11 03.19.41.000000000 PM','DD-MON-YY HH24:M
I:SS'),'1027','51010','BIR')
                             *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string


SQL>

Regards

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513772 is a reply to message #513769]

Wed, 29 June 2011 06:13

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your data doesn't match the format does it?
As I said your data is timestamp, you need to either:
a) make the column a timestamp.
b) get rid of the fractional seconds in your data.

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513775 is a reply to message #513772]

Wed, 29 June 2011 06:39

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi,

now I changed the Column to timestamp. below is the current table structure.

SQL> desc tbl_force_charging;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 ANI                                                VARCHAR2(10)
 DATE_TIME                                          TIMESTAMP(6)
 DURATIONS                                          VARCHAR2(10)
 SRC                                                VARCHAR2(10)
 CIRCLEID                                           VARCHAR2(10)

After This using the command, I am facing the error.

SQL> ed
Wrote file afiedt.buf

  1  insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
  2* values ('9569333585',to_date('29-JUN-11 03.19.41.000000000 PM','DD-MON-YY H
H24:MI:SS'),'1027','51010','BIR')
SQL> /
values ('9569333585',to_date('29-JUN-11 03.19.41.000000000 PM','DD-MON-YY HH24:M
I:SS'),'1027','51010','BIR')
                             *
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

Regards

Pradeep

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513777 is a reply to message #513775]

Wed, 29 June 2011 06:44

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ TO_DATE must ALWAYS be used with a format.
2/ The format MUST match the data.

Do you think that:
'DD-MON-YY HH24:MI:SS
matches with
'29-JUN-11 03.19.41.000000000 PM'
?

Please read Datetime Format Models.

Regards
Michel

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513780 is a reply to message #513777]

Wed, 29 June 2011 06:51

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michal,

I am not able to find out the same in mentioned linked. Kindly let me the exact data format for this insert query.

Thanks

Pradeep

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513782 is a reply to message #513780]

Wed, 29 June 2011 06:55

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

You didn't look very hard did you?
That page of the docs lists all the format elememnts and what they represent. If you don't understand that you'll never be able to work with dates or timestamps properly. So instead of begging us to spoonfeed you I suggest you spend a bit more time reading it.

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513783 is a reply to message #513780]

Wed, 29 June 2011 06:55

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select to_date('29-JUN-11 03.19.41.000000000 PM',
  2                 'DD-MON-YY HH:MI:SS".000000000" PM') d
  3  from dual
  4  /

D
-------------------
29/06/2011 15:19:41

Regards
Michel

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513784 is a reply to message #513782]

Wed, 29 June 2011 06:57

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

cookiemonster wrote on Wed, 29 June 2011 13:55

You didn't look very hard did you?
That page of the docs lists all the format elememnts and what they represent. If you don't understand that you'll never be able to work with dates or timestamps properly. So instead of begging us to spoonfeed you I suggest you spend a bit more time reading it.

Sorry, I think the same but was tired to repeat the same thing, it is less effort to provide the format string. Smile

Regards
Michel

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #513786 is a reply to message #513783]

Wed, 29 June 2011 07:02

pradies
Messages: 250
Registered: May 2008

Senior Member

Hi Michal,

I am not begging you to solute this prob. I always try to google and solve it at own basis by either googling or any book/guide. I normally not go to forum until or unless I didn't reached to the timeline.

So Don't say to any body. You are a good responser.

Ragards

Pradeep

Report message to a moderator

Previous Topic:	Putting correct records in Table and Updating.
Next Topic:	remove whitespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Aug 26 20:20:05 CDT 2025