Eclipse Community Forums: BIRT » Date comparison not working with Oracle

Help

Home

Home » Archived » BIRT » Date comparison not working with Oracle(SQL database datetime comparison)

Show: Today's Messages :: Show Polls :: Message Navigator

Date comparison not working with Oracle [message #657166]

Tue, 01 March 2011 19:09

Olly

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

Report message to a moderator

Re: Date comparison not working with Oracle [message #657169 is a reply to message #657166]

Tue, 01 March 2011 19:28

Richard A. Polunsky

Messages: 212
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.

Report message to a moderator

Re: Date comparison not working with Oracle [message #657445 is a reply to message #657169]

Wed, 02 March 2011 19:35

Olly

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

Richard A. Polunsky

Messages: 212
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.

Report message to a moderator