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
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: 33124

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]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

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
It's not a secret anymore!