Converting MySQL Datetime to MS SQL Datetime and vice versa !

[Fidel Edwards]

Hi folks,

I want to convert MySQL datetime(yyyy-mm-dd hh:mm:ss) to MS SQL datetime(mm-dd-yyyy hh:mm:ss) . I googled it hardly but no success .

Please help me !

Thanks in advance !

[Rob Spoor]

How about two SimpleDateFormat objects? You use the first to convert the MySQL date string into a Date object, then use the second to convert that Date object back into a string.

However, if you retrieve the values directly from the database, you shouldn't use Strings at all. ResultSet and PreparedStatement can work with Date objects: java.sql.Date for the date only, java.sql.Time for the time only, and java.sql.Timestamp for both date and time. All three classes extend java.util.Date. Conversion between them can be done with the constructors:

Timestamp ts = new Timestamp(date.getTime());

[Fidel Edwards]

Rob Prime wrote:

Timestamp ts = new Timestamp(date.getTime());

Thanks Rob ! For Early reply !

Meanwhile I also tried a new thing as

 
// Data Coming from mySQl Connection
long insertDate = resultSetSync.getDate("Inserted_Time").getTime();
   

During insertion I used

 String insertInto = "insert into test(Buyer_Id,insert_time) values (1,insertDate);// Inserting into MS SQL Database 

But here it is not working as showing

com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting expression to data type datetime.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
	at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
	at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
	at com.yuasa.dbsync.App.SynceTest1.doSynronizationBuyer(SynceTest1.java:69)
	at com.yuasa.dbsync.App.SynceTest1.main(SynceTest1.java:92)


    

[Rob Spoor]

Not all database systems handle dates the same. Some may use a full long, while others may only use half of that. That's why you should use the date/time types in the java.sql packages:

Timestamp insertDate = resultSetSync.getTimestamp("Inserted_Time");

String insertInto = "insert into test(Buyer_Id,insert_time) values (?,?)"; // Inserting into MS SQL Database
PreparedStatement stmt = connection.prepareStatement(insertInto);
stmt.setInt(1, 1);
stmt.setTimestamp(2, insertDate);

Don't mix the types; use getTimestamp and setTimestamp for both date/time, not getDate. That may remove the time information. For a column called insert_time that's probably not what you want.

You may want to read http://java.sun.com/docs/books/tutorial/jdbc/index.html; especially the part about Prepared Statements.

[Fidel Edwards]

Rob Prime wrote:
You may want to read http://java.sun.com/docs/books/tutorial/jdbc/index.html; especially the part about Prepared Statements.

OOh Rob Tons of thanks !!
I can't tell you how happy I am . It was nice solution and tutorial.

Again thanks a lot .