Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Date comparison not working with Oracle(SQL database datetime comparison)
Date comparison not working with Oracle [message #657166] Tue, 01 March 2011 19:09 Go to next message
Olly   is currently offline Olly Friend
Messages: 61
Registered: June 2010
Location: Florida
Member
I am trying to design a report that will display information generated on a certain date and time. My sql query is as follows,
select *  from TABLE where ( TABLE_ATT = 'Minimum Data Value' or
TABLE_ATT = 'Maximum Data Value' or
TABLE_ATT = 'Latency' or 
TABLE_ATT= 'Standard Deviation' or
TABLE_ATT = 'Refresh Rate / Coverage Time' or
TABLE_ATT = 'Mean Data Value' )
AND TO_DATE(SCENE_TIME, 'DD-MON-YY HH:MI:SS PM')  = 
TO_DATE('21-FEB-11 03:45:05 PM', 'DD-MON-YY HH:MI:SS PM')

when I run the query on sqldeveloper the correct results are returned, but when I put this in my data source I get no matches. What could be the problem?
This is my nls session parameters on the Oracle database
DD-MON-RR HH:MI:SS AM
Re: Date comparison not working with Oracle [message #657169 is a reply to message #657166] Tue, 01 March 2011 19:28 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
Messages: 199
Registered: July 2009
Location: Houston TX
Senior Member

Am I correct in assuming that your SCENE_TIME column is of type VARCHAR2? And why are you using -RR in the NLS format but -YY in your query?

I would debug this first as
SELECT TABLE.*,
TO_DATE(SCENE_TIME, 'DD-MON-YY HH:MI:SS PM')
FROM TABLE

and see what you appear to get.
Re: Date comparison not working with Oracle [message #657445 is a reply to message #657169] Wed, 02 March 2011 19:35 Go to previous messageGo to next message
Olly   is currently offline Olly Friend
Messages: 61
Registered: June 2010
Location: Florida
Member
Incorrect, SCENE_TIME is of DATE format. When I run the following query
SELECT TABLE.*,
TO_DATE(SCENE_TIME, 'DD-MON-YY HH:MI:SS PM')
FROM TABLE

I get the date in this format on the generated report: Feb 21, 2011 6:30 PM on the eclipse side, on the sqldeveloper side I get the expected: 21-FEB-11 06:30:05 PM. Also, after I pasted the nls settings I noticed the RR and changed that to YY, so I had realized that error but forgot to correct it on the forum.

[Updated on: Wed, 02 March 2011 19:49]

Report message to a moderator

Re: Date comparison not working with Oracle [message #657454 is a reply to message #657166] Wed, 02 March 2011 20:13 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
Messages: 199
Registered: July 2009
Location: Houston TX
Senior Member

Well, TO_DATE expects a VARCHAR argument, so what's probably happening is that Oracle is converting SCENE_DATE to VARCHAR behind the scenes truncating the seconds.

Try filtering as:
AND SCENE_TIME =
TO_DATE('21-FEB-11 03:45:05 PM', 'DD-MON-YY HH:MI:SS PM')

although if the DATE resolution is finer than seconds, that won't work.

Guaranteed to work should be:
AND TO_CHAR(SCENE_TIME,'DD-MON-YY HH:MI:SS PM') = '21-FEB-11 03:45:05 PM' as that takes the internal resolution of the time element completely out of the picture.
Re: Date comparison not working with Oracle [message #657691 is a reply to message #657454] Thu, 03 March 2011 16:22 Go to previous messageGo to next message
Olly   is currently offline Olly Friend
Messages: 61
Registered: June 2010
Location: Florida
Member
Richard you were of great help. Thank you. The 1st filtering method worked. Now, is there a way to mark this as answered?
Re: Date comparison not working with Oracle [message #657703 is a reply to message #657166] Thu, 03 March 2011 16:38 Go to previous message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
Messages: 199
Registered: July 2009
Location: Houston TX
Senior Member

Not specifically. Your comment that the query now works does that. Glad to help.
Previous Topic:Birt viewer: default option "fit with page" in pdf export
Next Topic:Error reading configuration: Unable to create lock manager
Goto Forum:
  


Current Time: Fri Dec 19 12:59:13 GMT 2014

Powered by FUDForum. Page generated in 0.07200 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software