Home » SQL & PL/SQL » SQL & PL/SQL » Problem with inserting a DATE value (merged & renamed by LF)
Problem with inserting a DATE value (merged & renamed by LF) [message #271414] Mon, 01 October 2007 11:11 Go to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
i have got a problem while executing a insert statement
INSERT INTO expensereport (ERNO,ERDESC,ERSUBMITDATE,ERSTATUSDATE,ERSTATUS,SUBMITUSERNO,APPRUSERNO) VALUES (expensereport_seq.nextval,'Sales Presentation',2007-08-10T00:00:00, 2007-08-26T00:00:00,'APPROVED',2003,2004);
and got an error "missing comma"
Can anyone help me to sort this out?
many thanks

cheers
ryju
Re: insertn statement [message #271425 is a reply to message #271414] Mon, 01 October 2007 11:28 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
For starters, try using a to_date around the date values you are trying to insert

You might want to post a describe of the table you are trying to insert into.

[Updated on: Mon, 01 October 2007 11:28]

Report message to a moderator

Re: insertn statement [message #271428 is a reply to message #271414] Mon, 01 October 2007 11:46 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You've been here for quite a while now and you really should read the OraFAQ Forum Guide and learn how to properly ask a question.

A line of code you posted is unreadable. How do you expect someone to "torture" his/her eyes and find the missing comma for you?!? If it was properly formatted (using the [CODE] tags), it would be much more clear.

Marc has already told you what to do. I'd just like to ask what's this character doing here?
Quote:
2007-08-10T00:00:00
Re: insertn statement [message #271429 is a reply to message #271414] Mon, 01 October 2007 11:53 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
sorry for the unformatted version of my sql code.I think the problem is not with comma but something related with date attributes.I am trying to insert some data into a table with attributes number,varchar,date,date,number,number)..Thanks anyway.

cheers
ryju
Re: insertn statement [message #271430 is a reply to message #271414] Mon, 01 October 2007 11:54 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
the character T indicates time.
Re: insertn statement [message #271434 is a reply to message #271414] Mon, 01 October 2007 12:04 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
i tried to execute this code using to_date function,
 INSERT INTO expensereport (ERNO,ERDESC,ERSUBMITDATE,ERSTATUSDATE,ERSTATUS,SUBMITUSERNO,APPRUSERNO) 
VALUES (expensereport_seq.nextval, ?Sales Presentation',to_date('2007-08-10T00:00:00'),to_date('2007-08-26T00:00:00'),'APPROVED',2003,2004); 

but still got an error saying invalid character.
Please help me.
thanks
Ryju

[Updated on: Mon, 01 October 2007 13:06] by Moderator

Report message to a moderator

Re: insertn statement [message #271436 is a reply to message #271414] Mon, 01 October 2007 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref408
Re: insertn statement [message #271437 is a reply to message #271414] Mon, 01 October 2007 12:07 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Read up on TO_DATE.

And set your linesize to 80 .

Re: insertn statement [message #271444 is a reply to message #271414] Mon, 01 October 2007 12:25 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
i tried several ways like
 INSERT INTO expensereport ( ERNO
, ERDESC
, ERSUBMITDATE
, ERSTATUSDATE
, ERSTATUS
, SUBMITUSERNO
, APPRUSERNO
) VALUES
( expensereport_seq.nextval
, ?Sales Presentation'
, to_date('2007/08/10','yyyy/mm/dd')
, to_date('2007/08/26','yyyy/mm/dd')
, 'APPROVED'
, 2003
, 2004
);

but still it shows error like invalid character
can anyone help me?

thanks
ryju


[Mod-Edit: Formatted; and look what happened to the invalid character!]

[Updated on: Mon, 01 October 2007 13:06] by Moderator

Report message to a moderator

Re: insertn statement [message #271448 is a reply to message #271444] Mon, 01 October 2007 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you still don't understand what "80 characters per line" means.

Regards
Michel
Re: insertn statement [message #271452 is a reply to message #271414] Mon, 01 October 2007 12:36 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
ya.I didnt get the idea of line zize to 80.can u give me some hint?
Re: insertn statement [message #271454 is a reply to message #271444] Mon, 01 October 2007 12:38 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
RyjuRaju wrote on Mon, 01 October 2007 13:25
but still it shows error like invalid character



I see an invalid character at 135th position.
Re: insertn statement [message #271455 is a reply to message #271454] Mon, 01 October 2007 12:42 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
can u tell me exactly where it is as i cant find this 135th pos illegal character?
Re: insertn statement [message #271458 is a reply to message #271444] Mon, 01 October 2007 12:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
copy and paste here in code tags your sqlplus session where you issued this command.
There should be an indication which character sql thinks is the offending one.

[Edit: hehe, forget that, just saw what joy_division meant. (See? I CAN count)]

[Updated on: Mon, 01 October 2007 13:03]

Report message to a moderator

Re: insertn statement [message #271460 is a reply to message #271452] Mon, 01 October 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
00000000011111111112222222222333333333344444444445555555555666666666677777777778
12345678901234567890123456789012345678901234567890123456789012345678901234567890
                                                                               ^
                                                                               |

There, it is the 80th character.
Your post MUST not be wider than that.
Use the Preview Message button BEFORE clicking on "Submit Reply".
Is this clear?

Regards
Michel
date format [message #271471 is a reply to message #271414] Mon, 01 October 2007 13:35 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
can anyone tell me whether "2007-08-10T00:00:00" this date data has any problem.please dont tell me any links as i am fed up with search for last couple of hours.Any ideas?Thanks

Ryju
Re: date format [message #271474 is a reply to message #271471] Mon, 01 October 2007 13:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why the 'T'?
Re: date format [message #271476 is a reply to message #271474] Mon, 01 October 2007 13:40 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
T for representing the time.Can i exclude this T from the data.Any problem?
Re: date format [message #271479 is a reply to message #271476] Mon, 01 October 2007 13:43 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
First thing is that '2007-08-10T00:00:00"' isn't a date, even without the useless T it still isn't a date:

'2007-08-10 00:00:00' is a string

to_date('2007-08-10 00:00:00' , 'yyyy-mm-dd') Is a date (it may be the wrong date tho' as the day and month might be mixed up)
Re: insertn statement [message #271480 is a reply to message #271444] Mon, 01 October 2007 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is "?" in your query?

Regards
Michel
Re: date format [message #271481 is a reply to message #271471] Mon, 01 October 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It has no problem as it is not a date but a string.
Now if you explain what is your problem maybe we can usefully answer you.

Don't forget to always post your Oracle version (4 decimals).
(and to format with no more than 80 characters per line.)


Regards
Michel
Re: date format [message #271483 is a reply to message #271471] Mon, 01 October 2007 13:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why did you start a new thread. Several other people (very knowledgeable people I might add) were already helping you in another thread. It is completely ignorant to just ignore them and decide to start a new thread.
Please don't do that again.
Re: date format [message #271485 is a reply to message #271471] Mon, 01 October 2007 13:53 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
INSERT INTO expensereport (ERNO,ERDESC,ERSUBMITDATE,ERSTATUSDATE,ERSTATUS,SUBMITUSERNO,
APPRUSERNO) VALUES (expensereport_seq.nextval, 'Sales Presentation', 
to_date('2007-08-10 00:00:00' , 'yyyy-mm-dd'),
to_date('2007-08-26 00:00:00' , 'yyyy-mm-dd'),'APPROVED',2003,2004); 


this is the code i am trying to execute and i got a error message
"date format picture ends before converting entire input string"

Can anyone correct the error?
thanks
Ryju
Re: date format [message #271488 is a reply to message #271485] Mon, 01 October 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
* '2007-08-26 00:00:00' string date you tried to use.
* 'yyyy-mm-dd' format you use.
* "date format picture ends before converting entire input string" error message.

Conclusion?
Are you able to find it?

Regards
Michel


[Updated on: Mon, 01 October 2007 13:57]

Report message to a moderator

Re: date format [message #271489 is a reply to message #271485] Mon, 01 October 2007 13:57 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look at the string date '2007-08-10 00:00:00'.
look at the mask 'yyyy-mm-dd'

Do you see what is missing?
Re: date format [message #271491 is a reply to message #271471] Mon, 01 October 2007 13:58 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
can u tell me how to use the rest of the data(time data) along with this data?
cheers
ryju
Re: date format [message #271492 is a reply to message #271491] Mon, 01 October 2007 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you able to read the documentation about date format?

Regards
Michel
Re: date format [message #271493 is a reply to message #271491] Mon, 01 October 2007 14:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Nope. I will not do your work for you. You have to put some effort in
Re: date format [message #271495 is a reply to message #271493] Mon, 01 October 2007 14:13 Go to previous message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Thanks everybody.Finally i got it
Cheers
Ryju
Previous Topic: Finding all tables I have permission to read from...
Next Topic: ora00918
Goto Forum:
  


Current Time: Sun Dec 04 06:37:20 CST 2016

Total time taken to generate the page: 0.04028 seconds