CODEWITHSUNDEEP
sqloracle-database
Mixer
Mixer

Reputation: 359

Oracle 11g: compare DATE

Why these two select queries

create table tmp (d date);
insert into tmp (d) values (sysdate);

select * from tmp where d = sysdate;
select * from tmp where d = trunc(sysdate);

both return 0 rows?

select to_timestamp(d), to_timestamp(sysdate) from tmp;

clearly shows that both columns are equal:

TO_TIMESTAMP(D)             | TO_TIMESTAMP(SYSDATE)
July, 01 2013 00:00:00+0000 | July, 01 2013 00:00:00+0000

SQL Fiddle example

Upvotes: 1

Views: 3068

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26333

Your INSERT added a date with time of day, because SYSDATE includes a time component, accurate to one second.

Your first SELECT returned nothing because by the time you ran it the SYSDATE had a different value, a second or more older than the SYSDATE value at the time you did the INSERT.

Your second SELECT returned nothing because it didn't have a time of day. As mishik pointed out, if you TRUNC the SYSDATE and the tmp.d value, you'll get a match.

The TO_TIMESTAMP function isn't what you want to use to verify values here. It ignores the time component of a date. This query...

SELECT TO_TIMESTAMP(TO_DATE('7/1/2013 12:34:56', 'MM/DD/YYYY HH24:MI:SS'))
FROM DUAL;

... will return the date 7/2/2013 only, without a time component.

To see what was really inserted, do something like this to see the time component of each value:

SELECT TO_CHAR(d, 'MM/DD/YYYY HH24:MI:SS') FROM tmp;

Upvotes: 3

mishik
mishik

Reputation: 10003

When comparing trunc(sysdate) - you should trunc d too:

select * from tmp where trunc(d) = trunc(sysdate);

Simple d=sysdate will not work, because sysdate has changed since insert.

Upvotes: 3

Related Questions