CODEWITHSUNDEEP
javasqlsql-serverparameterized-query
simonalexander2005
simonalexander2005

Reputation: 4577

Using CONVERT in a parameterised query

I have some java code that interacts with an Oracle database, and am converting it to work with SQL Server.

The code is:

String parameterisedSQL = "INSERT INTO T(a,b) VALUES (?,?)";
try (Connection con = DriverManager.getConnection(connectionString, databaseUser, databasePassword);
PreparedStatement stmt = con.prepareStatement(parameterisedSQL);)
{       
    stmt.setString(0,"test");
    stmt.setString(1,"CONVERT(DATE, '20201230', 112)");

    stmt.executeUpdate();
    return true;
}

Obviously when it was Oracle code, the code read stmt.setString(1,"TO_DATE('20201230','YYYYMMDD')");

The Oracle code against an Oracle database worked fine, but now I am running the SQL Server code against a SQL Server database, I get an exception

Conversion failed when converting date and/or time from character string

I am assuming from this that SQL Server isn't parsing the CONVERT correctly as a parameter. Is there any way to get it to do this? (My code is actually more general than shown, so it would be hard to use .setDate, etc.)

Upvotes: 0

Views: 619

Answers (3)

Andreas
Andreas

Reputation: 159114

The question and both other answers are sub-optimal. You shouldn't send a date value to the database as a string. You should send it as a date value.

In this case, since it's a date-only value, without a time part, you would use java.sql.Date or java.time.LocalDate, depending on the capabilities of the JDBC driver.

Also note that parameter indexes are 1-base in the JDBC APIs.

// Old code: Wrong
stmt.setString(0,"test");
stmt.setString(1,"CONVERT(DATE, '20201230', 112)");

// Using java.sql.Date
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
stmt.setString(1, "test");
stmt.setDate(2, new java.sql.Date(fmt.parse("20201230")));

// Using java.time.LocalDate
DateTimeFormatter fmt = new DateTimeFormatter.ofPattern("uuuuMMdd");
stmt.setString(1, "test");
stmt.setObject(2, LocalDate.parse("20201230", fmt));

Upvotes: 1

forpas
forpas

Reputation: 164099

The way that you pass the function is interpreted like this:

INSERT INTO T(a,b) VALUES ('test', 'CONVERT(DATE, '20201230', 112)')

which is obviously wrong, because you try to insert the value 'CONVERT(DATE, '20201230', 112)' in a date column.

You should do this:

String parameterisedSQL = "INSERT INTO T(a,b) SELECT ?, CONVERT(DATE, ?, 112)";

and:

stmt.setString(1,"test");
stmt.setString(2,"20201230");

The indices are 1 based.

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 12969

@forpas has given detailed answer.

Additionally, For your condition, you don't need to convert the short date format(ISO8601), before inserting to the table. You can directly insert.

About ISO8601 date format

ISO 8601 Description YYYY-MM-DD

YYYYMMDD Same as the SQL standard. This format is the only format defined as an international standard.

declare @TABLE table(T date)

insert into @TABLE (T) VALUES('20201230')

SELECT * FROM @table

for your case,

stmt.setString(0,"test");
stmt.setString(1,"20201230");

Upvotes: 1

Related Questions