i,ve a table, tb_aduan. in this table there is a field where i save a date. i save the date as system date. i want to retrieve all the info for certain date. how can i do that.
can someone help me.plzzzzzzzzzzzzzzzzzzzz

Originally posted by fisya
i,ve a table, tb_aduan. in this table there is a field where i save a date. i save the date as system date. i want to retrieve all the info for certain date. how can i do that.
can someone help me.plzzzzzzzzzzzzzzzzzzzz

suppose x is the column name of ur table tb_aduan, then ur query shoud be this way

select * from tb_aduan
where to_date(x) = to_date('01-JAN-03')

suppose the certain date is 01-JAN-03

Praveen,

what would be the difference if we omit TO_DATE function and, therefore, simplify the query? (tb_aduan.x is of DATE type)
Query runned as

SELECT * FROM tb_aduan WHERE x = '01-jan-03';

returns just the same in my database. Are there situations where this wouldn't be the case?

Surely it can vary,

suppose the person has inserted date field in the table
as
insert into tb_aduan(id,x)
values(1001,sysdate)
/
This will insert the time stamp too.
To avoid such kind of surprises, its better to use to_date function.

Originally posted by praveenpr
This will insert the time stamp too.
To avoid such kind of surprises, its better to use to_date function.

Still I would prefer Truncate over to_Date.

in my situation, user will enter the date they want and system will retrieve it and preview it at the secong page. when i do this i got error that says :
mismatch......
how to solve this

Always use TO_DATE and an explicit format mask when comparing a date with a character string - and please use 4 digits for years!:

SELECT * FROM tb_aduan
WHERE x = TO_DATE('01-jan-2003','DD-MON-YYYY');

(It is not safe to assume that the default format mask is what you expect.)

Now, if the date column x could contain a time component and you want all x values for that date then you can either use TRUNC:

SELECT * FROM tb_aduan
WHERE TRUNC(x) = TO_DATE('01-jan-2003','DD-MON-YYYY');

or you can check by range:

SELECT * FROM tb_aduan
WHERE x >= TO_DATE('01-jan-2003','DD-MON-YYYY')
AND x < TO_DATE('01-jan-2003','DD-MON-YYYY')+1;

If you use TRUNC then be aware that Oracle cannot use an index on (tb_aduan.x) to answer the query. However, you could create an index on (TRUNC(tb_aduan.x)).