aspose file tools*
The moose likes JDBC and the fly likes PreparedStatement.setDate() is cutting the time part Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement.setDate() is cutting the time part" Watch "PreparedStatement.setDate() is cutting the time part" New topic
Author

PreparedStatement.setDate() is cutting the time part

Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Hi,

I'm passing a java.sql.Date to PreparedStatement.setDate() method.

void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
throws SQLException;

I double checked that my java.sql.Date called x contains the date, and the time part as well.
However, when call commit(), in the database i only see the date part, the time part contains zeros.

Why is that?

Thanks
Hebert Coelho
Ranch Hand

Joined: Jul 14, 2010
Posts: 754

How is your date column? It is set up to be date and time?


[uaiHebert.com] [Full WebApplication JSF EJB JPA JAAS with source code to download] One Table Per SubClass [Web/JSF]
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Hebert Coelho wrote:How is your date column? It is set up to be date and time?


It is an SQL DATE not TIMESTAMP
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
What i realized is that if i call setTimestamp() instead, i get the time information, but also millisecound which i don't want
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

So then setTimestamp is the method you should call. If you want the milliseconds set to zero, then do that before you call setTimestamp.
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:So then setTimestamp is the method you should call. If you want the milliseconds set to zero, then do that before you call setTimestamp.


Setting to just zero is not suitable for me, i want to totally get rid of milliseconds. I mean i not only use this logic for INSERT but also for SELECT.
When i'm doing a select i don't want millisecond appear in my queries.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

When you say "queries" I assume you're talking about Java code which gets data from the database. In which case it's completely up to you how you display the dates. You can display them with or without milliseconds. That has nothing to do with how you store the data in the database.
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:When you say "queries" I assume you're talking about Java code which gets data from the database. In which case it's completely up to you how you display the dates. You can display them with or without milliseconds. That has nothing to do with how you store the data in the database.


Yeah i know, but i'm binding the parameters with prepared statements. so i eather use setDate(), and have only date, or use setTimestamp(), and have milliseconds. I need something in between :)
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

So far I pointed out that (a) you can set the milliseconds to zero before you insert the timestamp into the database, and (b) you can display the timestamp without milliseconds after you extract it from the database.

As far as I can see that takes care of all your objections. You're going to have to explain what your problem is if it isn't one of those two.
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:So far I pointed out that (a) you can set the milliseconds to zero before you insert the timestamp into the database, and (b) you can display the timestamp without milliseconds after you extract it from the database.

As far as I can see that takes care of all your objections. You're going to have to explain what your problem is if it isn't one of those two.


Paul, we are not on the same page.

(a) i perfectly understand
(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

Gabriel Beres wrote:Paul, we are not on the same page.

(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.


I have said twice that you could set the milliseconds of a timestamp to zero before inserting that timestamp into the database. What you're saying doesn't make any sense. The database doesn't store anything in any format at all. It's only when you extract something from the database and display it that formatting takes place. So you're right, I don't know what page you're on. You're going to have to explain more carefully why you think formatting has anything to do with storing the data in the database.
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:
Gabriel Beres wrote:Paul, we are not on the same page.

(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.


I have said twice that you could set the milliseconds of a timestamp to zero before inserting that timestamp into the database. What you're saying doesn't make any sense. The database doesn't store anything in any format at all. It's only when you extract something from the database and display it that formatting takes place. So you're right, I don't know what page you're on. You're going to have to explain more carefully why you think formatting has anything to do with storing the data in the database.


Paul, let's forget about saving into the database. As i already said, i understand that part.

"It's only when you extract something from the database and display it that formatting takes place"

I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?



Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

Gabriel Beres wrote:I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?


I'm sorry, I find that question too confusing to answer. Let me say a few things about it and maybe we can figure out some other question.

First, I don't understand what it means to "format a parameter". A parameter of what?

And second, if I were to format a parameter, whatever that means, I wouldn't do it by constructing a database query.

So as I said, I'm completely lost. Could you rephrase that question?
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:
Gabriel Beres wrote:I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?


I'm sorry, I find that question too confusing to answer. Let me say a few things about it and maybe we can figure out some other question.

First, I don't understand what it means to "format a parameter". A parameter of what?

And second, if I were to format a parameter, whatever that means, I wouldn't do it by constructing a database query.

So as I said, I'm completely lost. Could you rephrase that question?


Sure.

I want to construct a JDBC query by using PreparedStatement. For example SELECT * FROM TABLE WHERE mydate = ?. Mydate is a date and i want to bind it as parameter. In order to do that, i must call setTimestamp() method on PreparedStatement. However if i do that, and execute my query, my parameter translates to yyyyMMdd HHmmss + miliseconds. My goal is to get rid of the milliseconds.

I hope it is more understandable now.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

Now I understand.

And I've said several times already that you should just set the milliseconds part of the timestamp to zero before you pass it to the setTimestamp() method. You would use a Calendar object to do that. Here's an outline of how to do it:

Create a Calendar object.
Set its time to your "date" value.
Set the milliseconds to zero.
Get the Calendar's time value into a java.util.Date object.
Make a java.sql.Timestamp from that.
Gabriel Beres
Ranch Hand

Joined: Sep 09, 2006
Posts: 61
Paul Clapham wrote:Now I understand.

And I've said several times already that you should just set the milliseconds part of the timestamp to zero before you pass it to the setTimestamp() method. You would use a Calendar object to do that. Here's an outline of how to do it:

Create a Calendar object.
Set its time to your "date" value.
Set the milliseconds to zero.
Get the Calendar's time value into a java.util.Date object.
Make a java.sql.Timestamp from that.


Thanks, i will try that. I know you said i should set it to zero, but my understanding was, that the milliseconds would be still there, but with zero value.
My apologize for the miss understanding.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18675
    
    8

Gabriel Beres wrote:but my understanding was, that the milliseconds would be still there, but with zero value.


Well, yes. That's right, if you ask your timestamp what its milliseconds value is, it's going to say zero. It has to be something. It's just a number, after all. There isn't any concept of "not being there" for a number.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement.setDate() is cutting the time part