Ajit, I think I have found the solution. With the TO_CHAR function, you should be using the LIKE operator rather than =. This SQL appears to work:
SELECT * FROM RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001 AND TO_CHAR(END_DATE, 'MM/DD/YYYY') LIKE (select TO_CHAR(max(END_DATE), 'MM/DD/YYYY') from RT_RINGTONE_SCHEDULE) I believe you will find the END_DATE=11/11/2030 12:00:00 AM Regards, James --__--__-- Message: 11 Subject: Bug? in date comparison SAPDB-Kernel-version 7.3.30 -EXTREMELY URGENT Date: Wed, 26 Feb 2003 09:31:47 +0530 From: "ajit_cus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Hi can someone explain this, All Queries are executed in SQL MODE ORACLE using SQL STUDIO The TO_CHAR is just to work around Date time overflow in SQLTUDIO. select TO_CHAR(END_DATE,'MM/DD/YYYY HH:MI:SS AM') FROM RT_RINGTONE_SCHEDULE returns: 11/11/2030 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2001 12:00:00 AM 11/11/2001 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 12/15/2029 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM 11/11/2030 12:00:00 AM select TO_CHAR(max(END_DATE),'MM/DD/YYYY HH:MI:SS AM') FROM RT_RINGTONE_SCHEDULE 11/11/2030 12:00:00 AM which so far is correct then why does SELECT * FROM RT_RINGTONE_SCHEDULE WHERE CATEGORY_ID = 10001 AND END_DATE =(select max(END_DATE) from RT_RINGTONE_SCHEDULE) return no rows? I expect it to return the row containing END_DATE=11/11/2030 12:00:00 AM This is extremely urgent,pls get back to me ASAP, Regards, Ajit P.S: Just in case u need it this is the table DDL: CREATE TABLE RT_RINGTONE_SCHEDULE ( LOCATION_ID varchar2(10), CATEGORY_ID number(5) CONSTRAINT RT_RINGTONE_SCHEDULE_FK1 REFERENCES RT_CATEGORIES(CATEGORY_ID), RINGTONE_ID number(5) CONSTRAINT RT_RINGTONE_SCHEDULE_FK2 REFERENCES RT_TONE_INFO(RINGTONE_ID), START_DATE date, END_DATE date NOT NULL, SEQ_ORDER number(5), CONSTRAINT RT_RINGTONE_SCHEDULE_PK PRIMARY KEY(LOCATION_ID,CATEGORY_ID,RINGTONE_ID,START_DATE) ) --__--__-- _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general End of sapdb.general Digest _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
