Home » SQL & PL/SQL » SQL & PL/SQL » Newbie SELECT WHERE DATE (11G)
Newbie SELECT WHERE DATE [message #569093] Fri, 19 October 2012 16:37 Go to next message
Emad
Messages: 3
Registered: October 2012
Location: Texas
Junior Member
Hello
I am new to SQL for Oracle,
So I am trying to run a simple query
SELECT * FROM   ORCHP01DBO.WGN3EVENTAUDIT
WHERE   EVENTTIMESTAMP  = TO_DATE ('04/11/2012, 11:35:27 AM ') ;


Getting an error = ORA-01843: not a valid month

Here is how the EVENTTIMESTAMP column is formated
04/13/2000 10:10:48 AM

How can I correct it

[EDITED by LF: fixed funny font formatting, removed superfluous empty lines, applied [code] tags]

[Updated on: Sat, 20 October 2012 15:35] by Moderator

Report message to a moderator

Re: Newbie SELECT WHERE DATE [message #569094 is a reply to message #569093] Fri, 19 October 2012 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 22542
Registered: January 2009
Senior Member
TO_DATE() function benefits from having a format mask

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#sthref1659

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Newbie SELECT WHERE DATE [message #569103 is a reply to message #569094] Sat, 20 October 2012 07:21 Go to previous messageGo to next message
rita_c
Messages: 8
Registered: August 2010
Junior Member
Hi Emad,

Try

SELECT * FROM ORCHP01DBO.WGN3EVENTAUDIT
WHERE EVENTTIMESTAMP = TO_DATE ('04/11/2012 11:35:27 AM ','DD/MM/YYYY HH12:MI:SS AM') ;
Re: Newbie SELECT WHERE DATE [message #569109 is a reply to message #569103] Sat, 20 October 2012 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 24 September 2012 09:20
@rita_c, once more:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


In addition, BlackSwan gave the pointer for OP to find the solution.
Do NOT provide basic solutions, let people learn.
Spoonfeeding is NOT help.

Regards
Michel
Re: Newbie SELECT WHERE DATE [message #569244 is a reply to message #569109] Tue, 23 October 2012 10:59 Go to previous messageGo to next message
Emad
Messages: 3
Registered: October 2012
Location: Texas
Junior Member
Michel, you are absolutely right: "Spoon feeding does NOT help"
but sometimes, Spoon feeding is needed to be able to carry on.
Re: Newbie SELECT WHERE DATE [message #569247 is a reply to message #569244] Tue, 23 October 2012 11:19 Go to previous message
Emad
Messages: 3
Registered: October 2012
Location: Texas
Junior Member
This worked..

SELECT * FROM ORCHP01DBO.WGN3EVENTAUDIT
WHERE EVENTTIMESTAMP = TO_DATE ('04/11/2012, 11:35:27 AM ', 'MM/DD/YYYY HH:MI:SS AM') ;

Thanks
Previous Topic: Query to find usernames from their Ids
Next Topic: Syntax error
Goto Forum:
  


Current Time: Thu Jul 31 06:44:50 CDT 2014

Total time taken to generate the page: 0.09045 seconds