aspose file tools*
The moose likes Oracle/OAS and the fly likes Unable to fetch records by using 'where' clause for a date type field in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Unable to fetch records by using Watch "Unable to fetch records by using New topic
Author

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

Vinod Vinu
Ranch Hand

Joined: Aug 30, 2009
Posts: 217
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.


Vinod Kumar Nair
"Any fool can write code that a computer can understan. Good programmers write code that humans can understand."
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16



[My Blog]
All roads lead to JavaRanch
Vinod Vinu
Ranch Hand

Joined: Aug 30, 2009
Posts: 217
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

Joined: Nov 24, 2005
Posts: 14687
    
  16

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

Joined: Aug 30, 2009
Posts: 217
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

Joined: Aug 22, 2010
Posts: 3456
    
  47

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to fetch records by using 'where' clause for a date type field in sql
 
Similar Threads
Hibernate date in criteria
Column name too wide
date problem
this is my query running succesfully in database but in my java program rs.next() is false
ORA-06502: PL/SQL: numeric or value error: character string buffer too small