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: 62
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
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: 22691
Registered: January 2009
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'));
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: 58861
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.

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


Current Time: Sat Aug 23 07:21:30 CDT 2014

Total time taken to generate the page: 0.08539 seconds