如何从Oracle/PLSQL中的零件创建日期时间? [英] How to create a datetime from parts in Oracle/PLSQL?
问题描述
我有一个约会(4/30/2012),一个小时(4)和一分钟(45).我需要将其转换为Oracle日期时间值,以便在PLSQL/Oracle SELECT语句中进行比较.理想情况下,会有一个像这样的函数(C#,因为我知道的更好一些):
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):
var date = "4/30/2012";
var hour = "4";
var minute = "45";
DateTime myDateTime = convertDateTime(date, hour, minute);
我有一个包含三列的表格来组成事件的日期和时间.我有另一个表,其中包含根据其开始日期和结束日期可能不匹配的项目.在伪SQL中,这是我想要做的事情:
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:
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;
或选择名称和最高费用(从某个时间范围内的报告费用中选择) 价格低于$ 100的地方."
OR "Select name and max cost (from reported cost during a time range) where the price is less than $100."
是否存在一个可能执行上述convertDateTime
函数之类的功能?
Is there an existing function that might do something like the above convertDateTime
function?
推荐答案
连接您的日期和时间字段,并将其传递给TO_DATE(假设24小时制):
Concatenate your date and time fields and pass them to TO_DATE (assumes 24 hour clock time):
TO_DATE(date||' '||hour||':'||minute,'MM/DD/YYYY HH24:MI')
这会将您的日期/时间值转换为Oracle DATE类型.
This will convert your date/time values to an Oracle DATE type.
这篇关于如何从Oracle/PLSQL中的零件创建日期时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!