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

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

Date comparison not working with Oracle [message #657166]

Tue, 01 March 2011 14:09

Eclipse User

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 14:28

Eclipse User

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 14:35

Eclipse User

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 14:49] by Moderator

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

Wed, 02 March 2011 15:13

Eclipse User

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 11:22

Eclipse User

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 11:38

Eclipse User

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:

-=] Back to Top [=-

Current Time: Wed Jul 23 13:49:49 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter