Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Vinod Vinu
Ranch Hand
Posts: 217
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


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é
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Vinod Vinu
Ranch Hand
Posts: 217
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Christophe Verré wrote:



Hi chris,
It is however working with following sql query


it is not working with following sql query


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

thanks in advance
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 217
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic