oracle 我将如何批量更新日期和时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5944098/
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
How would I mass update date and time?
提问by Justin
I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the recv_date field to read 4/21/2011 7:00:00 AM.
我必须查询日期和时间的更新。我知道如何单独更新日期，但我无法将时间添加到函数中。现在，就目前而言，它显示为4/20/2011 1:32:07 PM。我需要 recv_date 字段来读取4/21/2011 7:00:00 AM.
My query so far is:
到目前为止，我的查询是：
UPDATE cxadmin.ro_hist
SET recv_date = '4/21/2011'
WHERE recv_serial_nbr = 'SABTSMSSD'
回答by OMG Ponies
SQL date formats are notoriously picky, requiring you to use TO_DATE to ensure that a string representation of a date is converted to an Oracle DATE data type:
SQL 日期格式是出了名的挑剔，要求您使用TO_DATE 来确保将日期的字符串表示形式转换为 Oracle DATE 数据类型：
UPDATE cxadmin.ro_hist
SET recv_date = TO_DATE('4/21/2011', 'MM/DD/YYYY')
WHERE recv_serial_nbr = 'SABTSMSSD'
Your example doesn't include the time portion:
您的示例不包括时间部分：
UPDATE cxadmin.ro_hist
SET recv_date = TO_DATE('4/21/2011 7:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
WHERE recv_serial_nbr = 'SABTSMSSD'
回答by Jeff Swensen
Have you tried?
你有没有尝试过？
update cxadmin.ro_hist
set recv_date = '4/21/2011 07:00:00 AM'
where recv_serial_nbr ='SABTSMSSD'
回答by Evgen
Use the to_timestamp('4/21/2011 7:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') instead of TO_DATE
使用 to_timestamp('4/21/2011 7:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM') 而不是 TO_DATE
