Date data type confusion ? 1

[rogers42]

Hi Folks,

Sample "CREATE_DATE" data: 01-JUN-08

Query #1
=========
select count(*)
from CM
where CREATE_DATE = SYSDATE


Query #2
========
select count(*)
from CM
where to_char(CREATE_DATE, 'DD-MON-YY') =
to_char(SYSDATE - 1, 'DD-MON-YY')

How come query #2 returns valid data while query #1 returns zero rows ?

Secondly, how can I tweak query #1 (to work), so I can avoid datatype conversions ?

Thanks in advance

rogers42

 

[karluk]

This is because Oracle dates include hours, minutes and seconds in addition to day, month and year. Therefore your first query will actually try to match on something like "1:05:17 p.m. June 2, 2008" (or whatever sysdate is set to when you run the query). To get your query 1 to work, you would have to modify it to discard hours, minutes and seconds. Something like the following should work:

select count(*)
from CM
where CREATE_DATE = trunc(SYSDATE)

 

[rogers42]

Hi,

Thanks for the prompt reply.

I think the "CREATE_DATE" also has a time component that I had to get rid of to make query #1 to work

select count(*)
from CM
where trunc(CREATE_DATE) = trunc(SYSDATE)

 

[cmmrfrds]

One thing to be aware of is, if the create_date is indexed then adding it in a function will cause the index to be bypassed obviating the benefit of the index. In that case a range test is better especially on a large table.

select count(*)
from CM
where (CREATE_DATE >= trunc(SYSDATE)
and CREATE_DATE < trunc(SYSDATE+1) )