Thanks for all your inputs.
Simple solution is, not to set date format for table column field. Then
it will work.

Select SYSDATE From Dual
Where SYSDATE > TO_DATE('4/18/02 04:04:55','MM/DD/YY hh24:mi:ss')

Thanks,
Anitha

-----Original Message-----
From: Tanuj Mittal
[mailto:oracledba-ezmlmshield-x50972812.[Email address protected]
Sent: Thursday, November 02, 2006 1:13 PM
To: LazyDBA Discussion
Subject: RE: Help : query on Date with timestamp comparision

Hi Anitha,

First of all check out the server date format and then format your query
accordingly.
When dealing with the date format in the case of comparisons you need to
be very cautious in dealing that. You may need to do a lot of R & D in
this context.

Use this query:-

SELECT e.release_date,rev_number --into max_release_date
FROM table1 a, table2 e
WHERE a.item = '382035497'
AND e.item(+) = a.component
AND a.ID ='382035528'
AND e.rev_number IS NOT NULL
AND ((TO_char (e.release_date, 'DD/MM/RRRR HH24:MI:SS') >=
TO_date('29/08/2001 21:42:30', 'DD/MM/RRRR HH24:MI:SS'))

AND (TO_char(e.release_date, 'DD/MM/RRRR HH24:MI:SS') <
TO_date('02/10/2001 21:19:38', 'DD/MM/RRRR HH24:MI:SS')));

Thanks & Regards,

Tanuj Mittal



-----Original Message-----
From: Anitha Reddy
[mailto:oracledba-ezmlmshield-x19844468.[Email address protected]
Sent: Thursday, November 02, 2006 12:00 PM
To: LazyDBA Discussion
Subject: Help : query on Date with timestamp comparision

Hi,

Can you please help me with this query? I am trying to fetch the results
based on the date and timestamp, and checking for less than condition.
But query results in equal to also. Let me know where is the problem
with this query.

SELECT e.release_date,rev_number --into max_release_date
FROM table1 a, table2 e
WHERE a.item = '382035497'
AND e.item(+) = a.component
AND a.ID ='382035528'
AND e.rev_number IS NOT NULL
AND ((TO_date (e.release_date, 'DD/MM/RRRR HH24:MI:SS') >=
TO_date ('29/08/2001 21:42:30', 'DD/MM/RRRR HH24:MI:SS'))

AND (TO_date (e.release_date, 'DD/MM/RRRR HH24:MI:SS') <
TO_date('02/10/2001 21:19:38', 'DD/MM/RRRR HH24:MI:SS')));

Query results are:

e.release_date rev_number
--
10/2/2001 8:14:44 PM 10
10/2/2001 9:19:38 PM 10A

Actually I am expecting only one row

e.release_date rev_number
--
10/2/2001 8:14:44 PM 10

Thanks
Anitha

DISCLAIMER:"The information contained in this message and the
attachments (if any) may be privileged and confidential and protected
from disclosure. You are hereby notified that any unauthorized use,
dissemination, distribution or copying of this communication, review,
retransmission, or taking of any action based upon this information, by
persons or entities other than the intended recipient, is strictly
prohibited. If you are not the intended recipient or an employee or
agent responsible for delivering this message, and have received this
communication in error, please notify us immediately by replying to the
message and kindly delete the original message, attachments, if any, and
all its copies from your computer system. Thank you for your
cooperation."


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DISCLAIMER:"The information contained in this message and the attachments (if any) may be privileged and confidential and protected from disclosure. You are hereby notified that any unauthorized use, dissemination, distribution or copying of this communication, review, retransmission, or taking of any action based upon this information, by persons or entities other than the intended recipient, is strictly prohibited. If you are not the intended recipient or an employee or agent responsible for delivering this message, and have received this communication in error, please notify us immediately by replying to the message and kindly delete the original message, attachments, if any, and all its copies from your computer system. Thank you for your cooperation."

Oracle LazyDBA home page