Difficulties with querying a date

[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.

[Paul Campbell]

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]

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 ]