英文:
Using CONVERT in a parameterised query
问题
String parameterisedSQL = "INSERT INTO T(a,b) VALUES (?,?)";
try (Connection con = DriverManager.getConnection(connectionString, databaseUser, databasePassword);
PreparedStatement stmt = con.prepareStatement(parameterisedSQL);)
{
stmt.setString(1, "test");
stmt.setString(2, "CONVERT(DATE, '20201230', 112)");
stmt.executeUpdate();
return true;
}
显然,在使用Oracle代码时,代码是这样的:stmt.setString(2,"TO_DATE('20201230','YYYYMMDD')");
在针对Oracle数据库的情况下,这段代码能够正常工作。但是现在我正在针对SQL Server数据库运行这段代码时,会抛出一个异常:
Conversion failed when converting date and/or time from character string
从这个异常信息中,我猜测SQL Server没有正确地将CONVERT解析为参数。有没有办法让它这样做呢?(实际上,我的代码比显示的要更通用,所以很难使用.setDate
等方法。)
英文:
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.)
答案1
得分: 2
传递函数的方式被解释如下:
INSERT INTO T(a,b) VALUES ('test', 'CONVERT(DATE, '20201230', 112)')
这显然是错误的,因为你试图将值'CONVERT(DATE, '20201230', 112)'插入到'date'列中。
你应该这样做:
String parameterisedSQL = "INSERT INTO T(a,b) SELECT ?, CONVERT(DATE, ?, 112)";
以及:
stmt.setString(1,"test");
stmt.setString(2,"20201230");
索引是从1开始计数的。
英文:
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.<br/>
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.
答案2
得分: 1
@forpas已经给出了详细的答案。
此外,根据您的情况,您无需在将短日期格式(ISO8601)插入表格之前进行转换。您可以直接插入。
> ISO 8601 描述 YYYY-MM-DD
>
> YYYYMMDD 与SQL标准相同。该格式是唯一作为国际标准定义的格式。
declare @TABLE table(T date)
insert into @TABLE (T) VALUES('20201230')
SELECT * FROM @table
对于您的情况,
stmt.setString(0,"test");
stmt.setString(1,"20201230");
英文:
@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");
答案3
得分: 1
问题和另外两个答案都不是最优的。您不应将日期值作为字符串发送到数据库,而应将其作为日期值发送。
在这种情况下,由于它只是一个仅包含日期的值,没有时间部分,您应该使用 java.sql.Date
或 java.time.LocalDate
,具体取决于 JDBC 驱动程序的功能。
另外,请注意 JDBC API 中的参数索引是从 1 开始的。
// 旧代码:错误的
stmt.setString(1, "test");
stmt.setString(2, "CONVERT(DATE, '20201230', 112)");
// 使用 java.sql.Date
SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMdd");
stmt.setString(1, "test");
stmt.setDate(2, new java.sql.Date(fmt.parse("20201230").getTime()));
// 使用 java.time.LocalDate
DateTimeFormatter fmt = DateTimeFormatter.ofPattern("uuuuMMdd");
stmt.setString(1, "test");
stmt.setObject(2, LocalDate.parse("20201230", fmt));
英文:
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));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论