Date comparison not working with Oracle [message #657166] |
Tue, 01 March 2011 19:09 |
|
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 |
|
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 |
|
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 |
|
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.
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04191 seconds