MAX function to get recent date doesn't work as expected
719208Aug 25 2009 — edited Aug 25 2009Hi,
I recently started working on Oracle and came across this problem. I coded below given query to get the latest date from table and compare it with Sysdate + 1 timestamp. I am not able to understand what I coded wrong but the query gives me wrong results as explained below :
select NVL(MAX(to_char(max(last_update),'mm/dd/yyyy hh12:mi:ss AM')), to_char(sysdate + 1,'mm/dd/yyyy')||' 12:00:00 AM')
from Audit_Table_Name
where col1 = 'AA'
and location_type = 'STATE'
and original_flag = 'Y'
group by col1,location_type,original_flag;
Here Max(last_update) from Audit Table Contains value --> 08/25/2009 12:00:00 AM
Note : Data type of last_update is Date
so ideally speaking it should give 08/26/2009 12:00:00 AM but surprisingly it is giving 08/25/2009 12:00:00 AM as output. Anyone can explain why this is happening and what we need to do to get correct results?
Regards,
Amol