Oracle 仅从查询中的 To_Date() 获取时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7042230/
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
Oracle Get only time from To_Date() in a Query?
提问by Soren
I have an insert query:
我有一个插入查询：
INSERT INTO THE_TABLE (Field_A)
VALUES (TO_DATE('08/12/2011 08:35:42 AM','HH:MI:SS PM'))
WHERE Something = 'SomethingElse'
Field_A is a Date field. When I execute the query, Field_A still shows a date and time.
Field_A 是日期字段。当我执行查询时，Field_A 仍然显示日期和时间。
Is there a way to grab only the time?
有没有办法只抓住时间？
I have tried To_Char(), but then it converts to a string and the field wont take it.
我试过 To_Char()，但后来它转换为一个字符串并且该字段不会接受它。
I have also tried TO_DATE('08:35:42 AM','HH:MI:SS PM') and that doesn't work either, it still shows a date and time in the table.
我也试过 TO_DATE('08:35:42 AM','HH:MI:SS PM') 但这也不起作用，它仍然在表中显示日期和时间。
采纳答案by NullUserException
If your field is a date field, you will always have the date portion of your date time.
如果您的字段是日期字段，您将始终拥有日期时间的日期部分。
You could store it as something else, like just the number of seconds and use a conversion function to convert it to a time. Since it seems like TO_CHARwon't take a number and convert it a time, you'd have to do this on the application side.
您可以将其存储为其他内容，例如秒数，然后使用转换函数将其转换为时间。由于似乎TO_CHAR不需要一个数字并一次转换它，因此您必须在应用程序端执行此操作。
So I'd just store it as a DATEanyways to avoid confusion; just ignore the date portion when using it.
所以我只是将它存储为DATE反正以避免混淆；使用时只需忽略日期部分。
And by the way, this:
顺便说一下，这个：
I have tried To_Char(), but then it converts to a string and the field wont take it.
我试过 To_Char()，但后来它转换为一个字符串并且该字段不会接受它。
Is not quite right, because strings in the correct format are implicitly converted to DATEs.
不太对，因为正确格式的字符串被隐式转换为DATEs。
回答by Rajesh Chamarthi
TO_DATE('08:35:42 AM','HH:MI:SS PM')
The reason this doesn't work is because this not a complete date. Even when you use a to_date('07/12/2011','MM/DD/YYYY'), Oracle stores the date and time, but makes all the components of the time ZERO. So the actual date stored is 07/12/2011 HH:MI:SS
这不起作用的原因是因为这不是一个完整的日期。即使您使用 to_date('07/12/2011','MM/DD/YYYY')，Oracle 也会存储日期和时间，但会将时间的所有组件归零。所以实际存储的日期是 07/12/2011 HH:MI:SS
If you want to store the time component seperately, It should be a varchar field and you'll need to add it to the date part to get the complete date. Example..
如果你想单独存储时间组件，它应该是一个 varchar 字段，你需要将它添加到日期部分以获得完整的日期。例子..
Select to_date(
to_char(date_field_stored_as_date,'DD-MON-YYYY') ||
' ' ||
to_char(time_field_stored_as_varchar),
'DD-MON-YYYY HH24:MI:SS'
)
