Error when I set parameters to PreparedStatement

Question

I have MSSQL procedure:

my_proc

I get data from java

String sql = "exec my_proc '2016-01-01','2016-01-20','2016-01-01'";
stmt = connection.prepareStatement(sql);
rs = stmt.executeQuery();

and get all data fine!

but if I use

String msisdn, String startDate, String endDate

String sql = "exec my_proc ?,?,?";
        stmt = connection.prepareStatement(sql);
        stmt.setString(1, msisdn);
        stmt.setString(2, startDate);
        stmt.setString(3, endDate);
        rs = stmt.executeQuery();

I get error:

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to datetime.

In procedure I have variables:

@Startdtin datetime,
@Enddtin datetime,
@msisdnin varchar(18)

I tried use

stmt.setTimestamp(2, startDate); //startDate - convert to Timestamp

and

 stmt.setDate(2, startDate); //startDate - convert to Date (sql and util)

It is not helped. How pass date to PreparedStatementcorrectly`?

Possible duplicate of [Error converting data type varchar to datetime. when I tried stmt.executeQuery()](http://stackoverflow.com/questions/35358772/error-converting-data-type-varchar-to-datetime-when-i-tried-stmt-executequery) — JonK, Feb 12 '16 at 11:23
@Satya yes startDate is String wjth date for example '2016-01-01' — user5620472, Feb 12 '16 at 11:26
possible duplicate of http://stackoverflow.com/questions/1135746/sql-server-convert-string-to-datetime — DwB, Feb 12 '16 at 11:27

score 0 · Answer 1 · answered Feb 12 '16 at 11:26

Your startDate is of type String. So convert it into Date type.

java.util.Date myDate = new java.util.Date(startDate);
java.sql.Date sqlDate = new java.sql.Date(myDate.getTime());

-------------------------------
stmt.setDate(5, sqlDate);

Use CallableStatement to execute procedures.

score 0 · Answer 2 · answered Feb 12 '16 at 11:27

0

Once corrected the format of your data you have to change the code.

To execute a procedure you need to use a CallableStatement, not a PreparedStatement.

If you have your date in string format, first convert it to Date in java using a SimpleDateFormat.

Than simply set it as Date on the callableStatement.

String dateString = ...;
Date date = ...;

myCallableStatement.setDate(1, date);

answered Feb 12 '16 at 11:27

Davide Lorenzo MARINO

26,420
4
39
56

why String sql = "exec my_proc '2016-01-01','2016-01-20','2016-01-01'"; stmt = connection.prepareStatement(sql); rs = stmt.executeQuery(); work fine? – user5620472 Feb 12 '16 at 11:31
It depends from the type of database and the type of procedure. Procedures can accept parameters for both input and output (generally parameters named in the calls as IN or OUT). If your procedure doesn't use the parameters for output it should works also with a PreparedStatement, otherwise is not possible to take the results. Generaly is a best practice to use a CallableStatement when executing procedures and use a PreparedStatement only when executing standard queries – Davide Lorenzo MARINO Feb 12 '16 at 11:36

Error when I set parameters to PreparedStatement

2 Answers2