如何从 Oracle/PLSQL 中的部分创建日期时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11404064/
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 to create a datetime from parts in Oracle/PLSQL?
提问by Byron Sommardahl
I have a date (4/30/2012), an hour (4) and a minute (45). I need to convert that to an Oracle datetime value for comparison in a PLSQL/Oracle SELECT statement. Ideally, there would be a function like this (C# since I know it a little better):
我有一个日期 (4/30/2012)、一小时 (4) 和一分钟 (45)。我需要将其转换为 Oracle 日期时间值，以便在 PLSQL/Oracle SELECT 语句中进行比较。理想情况下，会有一个这样的函数（C#，因为我更了解它）：
var date = "4/30/2012";
var hour = "4";
var minute = "45";
DateTime myDateTime = convertDateTime(date, hour, minute);
I have a table with three columns to make up the date and time of an event. I have another table that has items that may or may not match based on their start and end dates. In pseudo SQL, here's what I'm trying to do:
我有一个包含三列的表格来组成事件的日期和时间。我有另一个表，其中包含根据开始和结束日期可能匹配或不匹配的项目。在伪 SQL 中，这就是我想要做的：
SELECT
G.Name,
(SELECT MAX(Cost)
FROM CrappyTable AS C
WHERE G.StartTime < convertDateTime(C.Date, C.Hour, C.Minute)
AND G.EndTime > convertDateTime(C.Date, C.Hour, C.Minute)) as Cost
FROM GoodTable as G
WHERE G.Price < 100.00;
OR "Select name and max cost (from reported cost during a time range) where the price is less than $100."
或“选择价格低于 100 美元的名称和最高成本（来自某个时间范围内报告的成本）。”
Is there an existing function that might do something like the above convertDateTimefunction?
是否存在可以执行上述convertDateTime功能的现有功能？
回答by DCookie
Concatenate your date and time fields and pass them to TO_DATE (assumes 24 hour clock time):
连接您的日期和时间字段并将它们传递给 TO_DATE（假设为 24 小时时钟时间）：
TO_DATE(date||' '||hour||':'||minute,'MM/DD/YYYY HH24:MI')
This will convert your date/time values to an Oracle DATE type.
这会将您的日期/时间值转换为 Oracle DATE 类型。
回答by Lee
Or:
或者：
TO_DATE(date, 'MM/DD/YYYY') + hour/24 + minute/1440
