Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01830: date format picture ends before converting entire input string (Oracle 11g)
ORA-01830: date format picture ends before converting entire input string [message #612127] Sat, 12 April 2014 09:46 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member
Hi

My requirement is the trunc of date,i want to cancatenated with current timestamp and insert into table. i am gettting the below error, can some one help me to fix this.

create table temp(start_date date) tablespace warehouse_big_data

insert into temp(start_date)
select cast(concat('12-Apr-2014 ',to_char(sysdate,'HH:MI:SS')) as date) from dual

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #612128 is a reply to message #612127] Sat, 12 April 2014 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
insert into temp(start_date)
VALUES( TO_DATE(('12-Apr-2014 ' || to_char(sysdate,'HH:MI:SS')),'DD-Mon-YYYY HH:MI:SS'));

Report message to a moderator

Re: ORA-01830: date format picture ends before converting entire input string [message #612138 is a reply to message #612127] Sat, 12 April 2014 13:53 Go to previous message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As for all languages casting should be used with very care and deeply knowing what you do.
There you cast a string as a date. A string is NOT a date so it is wrong.
What you have to do it is NOT to cast it is to CONVERT and this what does TO_DATE BlackSwan showed.

Report message to a moderator

Previous Topic: Multiple counts as one
Next Topic: PL SQL prime number program explanation.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 13:47:05 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.