oracle JDBC - ORA-01861: 文字与格式字符串 01861 不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43116673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JDBC - ORA-01861: literal does not match format string 01861
提问by Durga Prasad
I have problem with retrieving a data from oracle data base
I want to access data by using two JDatechoosers...
I use JDatechooserto store date value in database of type date.
我在从 oracle 数据库中检索数据时遇到问题
我想通过使用两个来访问数据JDatechoosers......
我用来JDatechooser在日期类型的数据库中存储日期值。
chooser=new JDateChooser();
chooser.setBounds (200, 175, 175, 25);
chooser.setDateFormatString("dd-MM-yyyy");
pstmt.setDate(5, new java.sql.Date(chooser.getDate().getTime()));
I want to access data between two dates using two JDatechooservalues as fallows..
我想使用两个JDatechooser值作为休假访问两个日期之间的数据。
chooser = new JDateChooser();
chooser.setBounds (100, 15, 100, 25);
chooser.setDateFormatString("dd-MM-yyyy");
chooser.addFocusListener (this);
chooser1 = new JDateChooser();
chooser1.setBounds (220, 15, 100, 25);
chooser1.setDateFormatString("dd-MM-yyyy");
chooser1.addFocusListener (this);
ResultSet rs = st.executeQuery("SELECT * FROM Bill WHERE B_DATE BETWEEN '"+new java.sql.Date(chooser.getDate().getTime())+"' AND '"+new java.sql.Date(chooser1.getDate().getTime())+"' ");
I get the error as
我得到的错误是
SQL Error: ORA-01861: literal does not match format string 01861
SQL Error: ORA-01861: literal does not match format string 01861
please help me to solve this
请帮我解决这个问题
回答by a_horse_with_no_name
Never pass DATE or TIMESTAMP values as as Strings.
切勿将 DATE 或 TIMESTAMP 值作为字符串传递。
Use a PreparedStatementand pass an instance of java.sql.Dateor java.sql.Timestamp
使用 aPreparedStatement并传递java.sql.Dateor的实例java.sql.Timestamp
PreparedStatement pstmt = conn.prepareStatement(
"SELECT * FROM Bill WHERE B_DATE BETWEEN ? and ?");
pstmt.setDate(1,new java.sql.Date(chooser.getDate().getTime()));
pstmt.setDate(2,new java.sql.Date(chooser1.getDate().getTime()));
ResultSet rs = st.executeQuery();
while (rs.next()) {
....
}
That way you never have to worry about formatting a date or timestamp value.
这样您就不必担心格式化日期或时间戳值。
As an Oracle DATEis actually a timestamp, you might be better off using a timestamp value.
由于 OracleDATE实际上是一个时间戳，因此最好使用时间戳值。
回答by siddharth chaudhary
whenever you want to Insert a date insert it with the help of PrepareStatement class because that has in inbuilt function to convert java.util.date (a whole timestamp with names and numbers )---->convert it to java.sql.Date(2018-09-06 format) and then use this sql date to preparedStatement as ps.setDate(format will become : 09-jan-1996) which is acceptable by oracle.
每当你想插入一个日期时，请在 PrepareStatement 类的帮助下插入它，因为它有内置函数来转换 java.util.date（带有名称和数字的整个时间戳）---->将其转换为 java.sql.Date （2018-09-06 格式）然后使用这个sql 日期到preparedStatement 作为ps.setDate（格式将变为：09-jan-1996）这是oracle 可以接受的。
Note: while converting util date to sql date pass time not the whole date.
注意：将 util 日期转换为 sql 日期时传递的不是整个日期。
