oracle 更新/检索/插入日期字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3433288/
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
Update/ Retrieve /Inserting date field
提问by maas
I am having difficulties while updating a date field into the Database. The field type in the DB is Date/Time.
我在将日期字段更新到数据库时遇到困难。DB 中的字段类型是日期/时间。
Now, I am trying to update the field name "R_Date".
现在，我正在尝试更新字段名称“R_Date”。
Currently, I am using the SQL Expression in my jsp"
UPDATE request SET request_date ='"+Request_Date+"'";, But it is not accepting.
目前，我在我的 jsp" 中使用 SQL 表达式
UPDATE request SET request_date ='"+Request_Date+"'";，但它不接受。
In the select statement I am using a normal select, I tried to use to_char or to_date, but it is not accepting the format of "DD-MMM-YYYY"
在我使用普通选择的选择语句中，我尝试使用 to_char 或 to_date，但它不接受“DD-MMM-YYYY”的格式
So, can you please help me to retrive/Update/Insert date field in the format of "DD-MMM-YYYY" the date field?
那么，您能帮我以“DD-MMM-YYYY”格式的日期字段检索/更新/插入日期字段吗？
回答by BalusC
The normal practice to store a timestamp in the DB (thus, java.util.Datein Java side and java.sql.Timestampin JDBC side) is to use PreparedStatement#setTimestamp().
在 DB（因此，java.util.Date在 Java 端和java.sql.TimestampJDBC 端）中存储时间戳的通常做法是使用PreparedStatement#setTimestamp().
Date requestDate = getItSomehow();
Timestamp timestamp = new Timestamp(requestDate.getTime());
preparedStatement = connection.prepareStatement("UPDATE request SET request_date = ?");
preparedStatement.setTimestamp(1, timestamp);
The normal practice to obtain a timestamp from the DB is to use ResultSet#getTimestamp().
从数据库获取时间戳的通常做法是使用ResultSet#getTimestamp().
Timestamp timestamp = resultSet.getTimestamp("request_date");
Date requestDate = timestamp; // You can just upcast.
To convert between java.util.Dateand java.lang.Stringyou normally use SimpleDateFormat:
要在java.util.Date和java.lang.String通常使用之间进行转换SimpleDateFormat：
// Convert from String to Date.
String requestDateAsString = "09-Aug-2010";
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
Date requestDate = sdf.parse(requestDateAsString);
// Convert from Date to String.
String anotherDateAsString = sdf.format(someDate);
See also:
也可以看看：
回答by AHungerArtist
I think you should use MON instead of MMM.
我认为您应该使用 MON 而不是 MMM。
Have you tried something like:
你有没有试过这样的事情：
UPDATE request
SET request_date = to_date('" + Request_Date + "', 'DD-MON-YYYY')
Hope you realize that as your statement stands (if it worked), it would update every row in the request table (not sure if that's your intention or not but I thought I'd point it out).
希望您意识到，正如您的声明一样（如果它有效），它会更新请求表中的每一行（不确定这是否是您的意图，但我想我会指出）。
回答by YoK
You need to check what date format you are trying to insert, and try using to_date method with appropriate format.
您需要检查您尝试插入的日期格式，并尝试使用具有适当格式的 to_date 方法。
Following is referenced from : http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
以下引用自：http: //infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
Oracle's default format for DATE is "DD-MON-YY". If you want to retrieve date in particular format you need to use :
Oracle 的默认日期格式是“DD-MON-YY”。如果要以特定格式检索日期，则需要使用：
TO_CHAR(<date>, '<format>')
Similarly if you need to insert/update date with input of date other than in standard format, you need to use :
同样，如果您需要使用标准格式以外的日期输入来插入/更新日期，则需要使用：
TO_DATE(<string>, '<format>')
where the <format>string can be formed from over 40 options. Some of the more popular ones include:
其中<format>字符串可以从超过40选项来形成。一些比较受欢迎的包括：
MM Numeric month (e.g., 07)
MON Abbreviated month name (e.g., JUL)
MONTH Full month name (e.g., JULY)
DD Day of month (e.g., 24)
DY Abbreviated name of day (e.g., FRI)
YYYY 4-digit year (e.g., 1998)
YY Last 2 digits of the year (e.g., 98)
RR Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM) Meridian indicator
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minute (0-59)
SS Second (0-59)
