wood burning stoves 2.0*
The moose likes JDBC and the fly likes Difficulties with querying a date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difficulties with querying a date" Watch "Difficulties with querying a date" New topic
Author

Difficulties with querying a date

Mike Himstead
Ranch Hand

Joined: Apr 12, 2006
Posts: 178
Hi,

I'd like to extract all data from an Oracle table created at a given date, the table has a DATE attribute which has hours and minutes etc. as well. My first try was:

SELECT *
FROM TABLE
WHERE TO_DATE(DATE, 'dd-mm-yyyy') = TO_DATE( ? , 'dd-mm-yyyy')

? will be filled with variables like '01.06.2007' (German date format here). Whatever I do, I don't get any results. I'm sure that is has something to do with the fact that the database column has hours and minutes as well while I only pass a "normal" date. How can I make Oracle to ignore hours and minutes.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Mike Himstead:
Hi,

I'd like to extract all data from an Oracle table created at a given date, the table has a DATE attribute which has hours and minutes etc. as well. My first try was:

SELECT *
FROM TABLE
WHERE TO_DATE(DATE, 'dd-mm-yyyy') = TO_DATE( ? , 'dd-mm-yyyy')

? will be filled with variables like '01.06.2007' (German date format here). Whatever I do, I don't get any results. I'm sure that is has something to do with the fact that the database column has hours and minutes as well while I only pass a "normal" date. How can I make Oracle to ignore hours and minutes.


try this.

select *
from table
where your_table_date_column = to_date( replace(?,'.','-') , 'dd-mm-yyyy')

/* oracle already knows your column is a date and its format. the replace removes the '.' and replaces with a dash '-' to match your format */
Mike Himstead
Ranch Hand

Joined: Apr 12, 2006
Posts: 178
I can't try it out right now (I'm at home), but if my query really looks like the one I posted above it can't work. Will have to check tomorrow.
[ November 19, 2007: Message edited by: Mike Himstead ]
 
 
subject: Difficulties with querying a date
 
Similar Threads
pass TO_DATE to preparestatement
DB2 Date format to Oracle Date format?
Oracle
oracle DATE
Date comparision in SQL from timestamp