This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difficulties with querying a date

 
Mike Himstead
Ranch Hand
Posts: 178
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic