Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difficulties with querying a date

 
Mike Himstead
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic