OraFAQ Forum: SQL & PL/SQL » Newbie SELECT WHERE DATE

Home » SQL & PL/SQL » SQL & PL/SQL » Newbie SELECT WHERE DATE (11G)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Newbie SELECT WHERE DATE [message #569093]

Fri, 19 October 2012 16:37

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

BlackSwan
Messages: 20245
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/

Report message to a moderator

Re: Newbie SELECT WHERE DATE [message #569103 is a reply to message #569094]

Sat, 20 October 2012 07:21

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') ;

Report message to a moderator

Re: Newbie SELECT WHERE DATE [message #569109 is a reply to message #569103]

Sat, 20 October 2012 10:05

Michel Cadot
Messages: 54712
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

Report message to a moderator

Re: Newbie SELECT WHERE DATE [message #569244 is a reply to message #569109]

Tue, 23 October 2012 10:59

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.

Report message to a moderator

Re: Newbie SELECT WHERE DATE [message #569247 is a reply to message #569244]

Tue, 23 October 2012 11:19

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 16:13:45 CDT 2013

Total time taken to generate the page: 0.26897 seconds