Forums Register Login

PreparedStatement.setDate() is cutting the time part

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
How is your date column? It is set up to be date and time?
+Pie Number of slices to send: Send
 

Hebert Coelho wrote:How is your date column? It is set up to be date and time?



It is an SQL DATE not TIMESTAMP
+Pie Number of slices to send: Send
What i realized is that if i call setTimestamp() instead, i get the time information, but also millisecound which i don't want
+Pie Number of slices to send: Send
So then setTimestamp is the method you should call. If you want the milliseconds set to zero, then do that before you call setTimestamp.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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 :)
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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?



+Pie Number of slices to send: Send
 

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?
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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.
There is no beard big enough to make me comfortable enough with my masculinity to wear pink. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 11147 times.
Similar Threads
How to create date object with this format 2008-08-08 10:12:35:333
Comparing two Dates
preparedStatement.setDate and java.util.Date
CallableStatement throwing data conversion error (Oracle)
Callable Statement
problem while inserting date field in oracle data base
Calendar
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 08:36:54.