Two Laptop Bag
The moose likes JDBC and Relational Databases and the fly likes Oracle cast()+PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Customer Requirements for Developers this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle cast()+PreparedStatement" Watch "Oracle cast()+PreparedStatement" New topic

Oracle cast()+PreparedStatement

Gabor Beres

Joined: Dec 21, 2004
Posts: 21

I have a SQL query: SELECT * FROM mytable WHERE mydate = :mydate

mydate is a TIMESTAMP, but i need to cast it to DATE using Oracle function cast(): SELECT * FROM mytable WHERE mydate = cast(:mydate as DATE)

Unfortunatelly i'm using PreparedStatements, so i set the parameter via method setTimestamp()

Is it possible to do the cast anyway?

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32328

It should be - you are still inserting a binding variable. Does that not work? What error does it give you?

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3717

The important question is - why do you need to cast? It can be problematic, as TIMESTAMP may contain millisecond (and possibly timezone), while DATE cannot. The cast will strip off this information if present and therefore might produce value different from what you set via setTimestamp(). If it happened, it would alter the results of your query.
I agree. Here's the link:
subject: Oracle cast()+PreparedStatement
jQuery in Action, 3rd edition