Skip to content
Advertisement

Oracle – wrong result using sysdate

I have the following query. This query does not return any result:

select snap_id from perfstat.stats$snapshot where snap_time = sysdate;

no rows selected

but if I format the snap_time and sysdate I receive the result:

select snap_id 
from perfstat.stats$snapshot 
where to_char(snap_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy');

SNAP_ID
----------
         1
         2
         3

Is there something wrong with my query? Why do I need format the date in this context?

obs: the column snap_time is date type.

Advertisement

Answer

The where clauses of the queries are not identical.

The first query tries to match snap_date against the current date and time. This is not likely to succeed, unless a snap was created at the very same second when the query is executed.

The second query compares the date part snap_date against the current day (the time part is not taken into account).

This:

where to_char(snap_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')

Actually means:

where trunc(snap_time) = trunc(sysdate)

Which can, (and should) be optimized as the clearer and more efficient:

where snap_time  >= trunc(sysdate)
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement