Home » SQL & PL/SQL » SQL & PL/SQL » ERROR in format
ERROR in format [message #238661] Fri, 18 May 2007 19:48 Go to next message
SUSAN1
Messages: 36
Registered: March 2007
Member
Hi,
Can you please se what is wrong and why Oracl doesnt accept "MAR", what is the format that I have to use for such query.
Sincerely/Susan

SQL> Select title, lending_timepoint from movie join lending
2 on movie.id=lending.item_id and lending_timepoint=06-mar-04;
on movie.id=lending.item_id and lending_timepoint=06-mar-04
*
ERROR at line 2:
ORA-00904: "MAR": invalid identifier


SQL>

[Updated on: Fri, 18 May 2007 19:49]

Report message to a moderator

Re: ERROR in format [message #238662 is a reply to message #238661] Fri, 18 May 2007 20:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I hope you are not unwilling or incapable of reading the fine SQL Reference manual found at http://tahiti.oracle.com

Pay close attention to the part regarding the TO_DATE function or just use GOOGLE.
Re: ERROR in format [message #238682 is a reply to message #238661] Sat, 19 May 2007 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Huumm, in any language 06-mar-04 means 6 minus the content of variable mar minus 4.

Regards
Michel

icon7.gif  Re: ERROR in format [message #238780 is a reply to message #238662] Sat, 19 May 2007 23:56 Go to previous messageGo to next message
SUSAN1
Messages: 36
Registered: March 2007
Member
Hi,
I was willing today Smile for reading the fine SQL Reference manual found at http://tahiti.oracle.com

And Thank you, it is a very usefull link for me.

Sincerely/Susan

[Updated on: Sat, 19 May 2007 23:57]

Report message to a moderator

Re: ERROR in format [message #238781 is a reply to message #238682] Sun, 20 May 2007 00:00 Go to previous messageGo to next message
SUSAN1
Messages: 36
Registered: March 2007
Member
You are right. What I was thinking!
It should look like this? Smile
'06-mar-04'
/Susan
Re: ERROR in format [message #238783 is a reply to message #238661] Sun, 20 May 2007 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select 'I am not a date data type 06-mar-04. I am just a string' from dual;

[Updated on: Sun, 20 May 2007 00:07] by Moderator

Report message to a moderator

Re: ERROR in format [message #238784 is a reply to message #238781] Sun, 20 May 2007 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a close look at TO_DATE function.

Regards
Michel
Re: ERROR in format [message #238823 is a reply to message #238784] Sun, 20 May 2007 03:57 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, although you have put single quotes around 06-mar-04, it is just a string, nothing more. If NLS settings were different, your query would fail anyway. When dealing with dates (which we represent by strings), always use the TO_DATE function. Doing so, you'll have a control over it.

Finally, the right way to do that would be (for example)
... and lending_timepoint = TO_DATE('06.03.2004', 'dd.mm.yyyy')
Re: ERROR in format [message #238866 is a reply to message #238661] Sun, 20 May 2007 13:34 Go to previous message
SUSAN1
Messages: 36
Registered: March 2007
Member
thank you.
Regards/Susan
Previous Topic: Nested Queries Type I and II
Next Topic: Temp table and cursor (merged topics)
Goto Forum:
  


Current Time: Sat Dec 03 22:00:29 CST 2016

Total time taken to generate the page: 0.08998 seconds