JavaServer Pages (JSP) and JSTL - how to insert date in sqlserver in (yyyy-MM-dd HH:mm:ss) forma
please help how to insert date in sqlserver in (yyyy-MM-dd HH:mm:ss) format, I have tried java.sql.Date but in that case the time portion automatically changed to 00:00:00 and while using java.sql.Timestamp it through me an error as data type mismatch in data base.
I have set data type as datetime in sqlserver instead of timestamp the reason is I have two datetime column in my table and if I use timestamp it will not allow to columns................
thanks in advance
Amit
Hi,
using now() function in mysql database sysdate in oracle.
or
java.util.Date dt = new java.util.Date();
java.sql.Date sdt = new java.sql.Date(dt.getYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes,getSeconds());
String rdate = sdt.toString();
System.out.println(rdate);
set data type of date field as timestampand use now()it will insert it into database as u want
Hi..thanks to both of you for ur response but the issue is I m taking values from user input form and I have converted those parameters to yyyy-MM-dd HH:mm:ss form using SimpleDateFormat so I guess using now() will not help me, second I cannot set datatype field as timestamp bcoz in sqlserver only one column is allowed as timestamp and I require start time and end time columns.....waiting for ur response or anyother suggestion
Regards,
Amit
when you say you are getting the values from user input you mean you ask user:
day? and gives you say 24?
String insert ="Insert INTO table(yourDate) Values(''"+year+"-"+month+"-"+day+"')";Statement stmt = con.createConnection();
ResultSet rs = stmt.executeUpdate(insert);
Otherwise please specify
datetime is the correct sql data type for your purpose. Timestamp is something completely different.
java.sql.Timestamp IS what you want to use. Theoretically it should map to a MSSQL datetime.
That detail should be handled by the JDBC driver.
What JDBC driver are you using?
Format of the date is irrelevant. You should create it as a java.sql.timestamp
So take your prepared statment
String sql = "insert into myTable(id, startTime, endTime) values (?,?,?)";PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, record.getId());
stmt.setTimestamp(2, ....)
stmt.setTimestamp(3, ...)
Given a java.util.Date, the best way to get a java.sql.Timestamp is via
new java.sql.Timestamp(date.getTime());
Hey thanks a lot buddy..its working fine...I more thing I like to knw if u can help me....is there a way to remove .f from timestamp like i m getting out in HH:mm:ss.f while printing timestamp..........Regards,Amit
How are you outputting the timestamp? With just a println()?
Run it through another SimpleDateFormat to convert it to string.
Basically you should communicate with the database using java.sql.Timestamp.
For displaying the value, use SimpleDateFormat to get the correct format.
Yes ur absolutly correct but the thing is now I have to retrieve the data which I have inserted ok lets say this way:
Select * from table where ? between StartTime and EndTime;
PreparedStatement ps = con.prepareStatement(qry);
ps.setTimestamp(1, starttime);
and so on.....
but the issue is I m not getting anything so checked the reason and found that timestamp setting the data in yyyy-MM-dd HH:mm:ss.f format and in my database its showing in yyyy-MM-dd HH:mm:ss AM/PM format (which I inserted through the option suggested by you earlier)
now plz suggest me how to retrieve the data....
Hey thanx a lot for ur support problem has been resolved....Gr8...Regards,Amit
