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.)
@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.
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");