Comparing Dates

I am trying to match exact dates in an SQL query in Oracle. Can someone please tell me why this query works:

SELECT count(*)
FROM sessions
where sess_date > to_date('2003-09-03', 'YYYY-MM-DD');

but this query does not:

SELECT count(*)
FROM sessions
where sess_date = to_date('2003-09-03', 'YYYY-MM-DD');

when I know the date does exist in the database. It's driving me mad.

I suspect that you would have better luck using:

select count(*)
from sessions
where to_char(sess_date, 'YYYY-MM-DD') = '2003-09-03';

As for why your second query doesn't work... well, the most likely problem is that sess_date has a time component that's not set to exactly midnight. I'd suggest using to_char rather than to_date, though, as it gives you a little better control over exactly what you're trying to compare.

Marty

It worked!

Thanks for your help martysb.

I usually do it via:

SELECT count(*)
FROM sessions
where trunc(sess_date) = to_date('2003-09-03', 'YYYY-MM-DD');

Like this we compare date fields and not character fields as martysb showed us.

Cheers,
Dan

Thanks Dan

That's great