jsp and mysql set time , date

Hello, friends,

I want to set a date/time field in a db table.The format will be : HH:mm yyyy-MM-dd .

java.util.Date currentDate =  new java.util.Date();  

in the sql statements:

insertStmt = sqlConn.prepareStatement( "UPDATE fileTable SET submitTime=?");
insertStmt.setDate( 1, new java.sql.Date(currentDate.getTime() ));
insertStmt.executeUpdate();

in the table, I found the time format is :yyyy-MM-dd. How could I get  HH:mm yyyy-MM-dd ?

Another related question is:  if I want to sort the table based on the time, can I implement like this :

selectStmt=sqlConn.prepareStatement("SELECT fileName FROM fileTable ORDER BY submitTime" );

Thanks for yor reply.


lilyyanAsked:
Who is Participating?
 
objectsConnect With a Mentor Commented:
The date type does not store time details, you'll need to use a a datetime or timestamp for that

the order by is correct to sort by date
0
 
objectsCommented:
what type is the database column?
0
 
lilyyanAuthor Commented:
Hi, thanks for your reply.
The field: submitTime datetype is date.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
lilyyanAuthor Commented:
Hello, thanks for the prompt response.

Now I changed the datatype to datetime.

If I use:  insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() ));

the result is in milliseconds ?
 
0
 
objectsCommented:
Use Timestamp instead of Time
0
 
lilyyanAuthor Commented:
Well, maybe those two ( Time or Timestamp) don't have much difference ?

From the PreparedStatement API:

setTime(int parameterIndex, Time x)
//  this will set the designated parameter to the given java.sql.Time value :

// the second paremter has to be Time datatype , it seems that I need use :

the method Time valueOf(String s)  

Another one :

setTimestamp(int parameterIndex, Timestamp x)  // simliar as above
//this will set the designated parameter to the given java.sql.Timestamp value.  it seems that I need use :

Timestamp valueOf(String s)  

??The question is : the format of the String s, how to get it ?


Since I need sort the table by the time order, I'm guessing that the vaule of  submitTime filed has to be milliseconds ?
0
 
objectsCommented:
You can use SimpleDateFormat class to format the date however you require it, that is independant of the database.
0
 
lilyyanAuthor Commented:
Is there an example for the format of Timestamp? Thanks
0
 
lilyyanAuthor Commented:
When I use :

insertStmt.setTimestamp(1 ,  new java.sql.Timestamp(currentDate.getTime() )); // when the submitTime datatype
                                                                                                                    //is timestamp
insertStmt.executeUpdate();

The value of submitTime will be 2005-04-19 22:31:47 . It's correct

If I  use :
insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() )); //when the submitTime datatype is datetime
insertStmt.executeUpdate();

The value of submitTime will be 0000-00-00 00:00:00. It's wrong.

But I didn't find setDatetime method in PreparedStatement API ?
0
 
objectsCommented:
use setTimestamp() for a datetime
0
 
lilyyanAuthor Commented:
haven't tried the above method

from PreparedStatement API
setTimestamp(int parameterIndex, Timestamp x)  // the second prameter has to be a Timestamp, but suppose submitTime datatype is datetime, how to solve this ? Thanks

0
 
lilyyanAuthor Commented:
Just tried : insertStmt.setTimestamp( 1, new java.sql.Time(currentDate.getTime() )); // it's failed :(
0
 
objectsCommented:
should be

 insertStmt.setTimestamp( 1, new java.sql.Timestamp(currentDate.getTime() ));
0
 
lilyyanAuthor Commented:
Yeah, when submitTime datatype is timestamp. if it's datetime, how to achieve ?

If I  use :
insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() )); //when the submitTime datatype is datetime
insertStmt.executeUpdate();

The value of submitTime will be 0000-00-00 00:00:00. It's wrong.

But I didn't find setDatetime method in PreparedStatement API ?
0
 
lilyyanAuthor Commented:
Many thanks for your attention. The problem is basically solved. I will close the question tomorrow.
0
 
objectsConnect With a Mentor Commented:
> Yeah, when submitTime datatype is timestamp. if it's datetime, how to achieve ?

From a java perspective I think you can treat them both the same
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.