Unable to fetch records by using 'where' clause for a date type field in sql

[Vinod Vinu]

Hi, i'm not good in sql. I'm using oracle as database. On Toad, i'm trying to fetch records using a 'where' clause for a data type field.

what i have done in the past is

select * from exchange_rate where updated_date = '12/16/2010'

I did not got any results here since i have realised that i had compared a date type with a string value.

updated_date is date type field with values like 12/16/2010 11:50:00 PM in the table exchange_rate

12 is month, 16 is date and 2010 ofcourse you know that it is year as format.

then i modified my query as
select * from exchange_rate where updated_date = to_date( '12/16/2010','mm/dd/yyyy')

but still nothing is getting. plese tell me where i'm doing mistake.

thanks in advance.

[Christophe Verré]

select * from exchange_rate where TO_CHAR(updated_date, 'MM/DD/YYYY') = '12/16/2010'

[Vinod Vinu]

Christophe Verré wrote:

select * from exchange_rate where TO_CHAR(updated_date, 'MM/DD/YYYY') = '12/16/2010'

Hi chris,
It is however working with following sql query

select * from som_spot_interface where to_char(INTERFACE_SENT_DATE, 'MM/DD/YYYY') = '12/16/2010'

it is not working with following sql query

select * from som_spot_interface where INTERFACE_SENT_DATE = TO_DATE( '12/16/2010','MM/DD/YYYY');

Any idea what is the difference in both queries and what is wrong in the second one ?

thanks in advance

[Christophe Verré]

The second one will only work if the time is 00:00:00. TO_DATE with MM/DD/YYYY generates a date whose time is 00:00:00. So you won't get records whose date looks like 12/16/2010 11:50:00 PM. Instead, you have to convert the date field into a character.

[Vinod Vinu]

Christophe Verré wrote:The second one will only work if the time is 00:00:00. TO_DATE with MM/DD/YYYY generates a date whose time is 00:00:00. So you won't get records whose date looks like 12/16/2010 11:50:00 PM. Instead, you have to convert the date field into a character.

really appreciated.
I will always keep this in my mind.

thanks for your support

[Martin Vajsar]

You might also be interested in the TRUNC function used on dates (see Oracle documentation). It is especially usefull if you need to do some date arithmetics; for simple WHERE clause it is probably no better than Christophe's solution.