mysql插入timestamp,如何在数据库中插入Timestamp？

I am getting this error:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd

hh:mm:ss[.fffffffff]

Here is my java code:

String sdate = request.getParameter("sdate");

//Timestamp ts=request.get

//out.println(sdate);

String text = "0000-00-00 00:00:00";

Timestamp ts = Timestamp.valueOf(text);

// Timestamp ts1=Timestamp.valueOf(sdate);

// Timestamp ts2=Timestamp.valueOf(edate);

try{

String s;

Format formatter;

Date date = new Date(sdate);

//out.println(date);

formatter = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");

s = formatter.format(date);

out.println(s);

PreparedStatement ps=cn.prepareStatement("insert into c1 values(?)");

// ps.setString(1,ts);

ps.setTimestamp(1,ts);

int i=ps.executeUpdate();

if(i==1)

{

pw.println("record inserted");

}

else

{

pw.println("not iNSERTED");

}

//cn.close();

}

catch(Exception e)

{

out.println(e.toString());

}

%>

Any Suggestions?

解决方案

Shakti, you need to address the error. java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

and then have a look at the way you've formatted your date.

You'll need to change your line below

formatter = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");

to:

formatter = SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Notice the difference and use of the hypen character in the date instead of the colon character which youre using.

UPDATE 1

Ok. so after @sotirios' comment, I decided to do some extra research and I came across this post and learned something. You cant give a 0000-00... date configuration because MySQL [uses "zero dates" as special placeholder

The solution plain and simple is to change the date string from

String text = "0000-00-00 00:00:00";

to atleast

String text = "0001-01-01 00:00:00";

And if you really want to send 0000-00-... date strings I believe the solution is to specify "zeroDateTimeBehavior=convertToNull" as a parameter to your MySQL connection (either in datasource URL or as an additional property), e.g.:

jdbc:mysql://localhost/myDatabase?zeroDateTimeBehavior=convertToNull

This will cause all such values to be sent as NULLs.