您如何选择"HH24:MI:SSxFF6"格式的varchar2字段作为秒间隔(6)? [英] How do you select a varchar2 field in the format of 'HH24:MI:SSxFF6' as an INTERVAL HOUR TO SECOND(6)?
问题描述
以下查询成功:
SELECT INTERVAL '00:00:00.000000' HOUR TO SECOND(6)
FROM DUAL;
以下失败:
SELECT INTERVAL time_field HOUR TO SECOND(6) -- time_field is a VARCHAR2(15)
FROM some_table;
出现以下错误:
ORA-00923: FROM keyword not found where expected
如何解决第二个查询以将HOUR设置为秒间隔?
How do I fix my second query to pull back an HOUR TO SECOND INTERVAL?
当我尝试时:
SELECT cast(time_field AS INTERVAL HOUR TO SECOND(6))
FROM some_table
我得到以下信息:
ORA-00963: unsupported interval type
测试设置:
CREATE TABLE some_table (
time_field VARCHAR2(50)
);
INSERT ALL
INTO some_table (time_field) VALUES ('10:00:00.000000')
INTO some_table (time_field) VALUES ('12:00:00.000000')
INTO some_table (time_field) VALUES ('15:00:00.000000')
INTO some_table (time_field) VALUES ('17:00:00.000000')
INTO some_table (time_field) VALUES ('20:00:00.000000')
INTO some_table (time_field) VALUES (NULL)
SELECT * FROM DUAL;
推荐答案
您可以使用 TO_DSINTERVAL
函数;使用CTE复制表的示例:
You can use the TO_DSINTERVAL
function; example here using a CTE to replicate your table:
WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT TO_DSINTERVAL('0 ' || time_field)
FROM some_table;
TO_DSINTERVAL('0'||TIME_FIELD)
---------------------------------------------------------------------------
+000000000 00:00:00.000000000
请注意,要使格式能够被函数识别,您必须提供天"部分,因此必须在虚拟'0 '
字符串的前面.
Note that to make the format something the function recognises, you have to supply a 'days' part, hence the prepending of the dummy '0 '
string.
您的固定值查询返回的格式略有不同(或至少显示的格式略有不同;
Your fixed-value query returns a slightly different format (or at least displays slightly differently; interval daa types do not have format models in quite the same way as dates, so not sure how to express that accurately):
SELECT INTERVAL '00:00:00.000000' HOUR TO SECOND(6)
FROM DUAL;
INTERVAL'00:00:00.000000'HOURTOSECOND(6)
---------------------------------------------------------------------------
+00 00:00:00.000000
要进行复制,可以在需要时进行cast
To replicate that you can cast
this if you need to:
WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT CAST(TO_DSINTERVAL('0 ' || time_field) AS INTERVAL DAY TO SECOND(6))
FROM some_table;
CAST(TO_DSINTERVAL('0'||TIME_FIELD)ASINTERVALDAYTOSECOND(6))
---------------------------------------------------------------------------
+00 00:00:00.000000
...或者仅仅是:
WITH some_table AS (SELECT '00:00:00.000000' AS time_field FROM dual)
SELECT CAST('0 ' || time_field AS INTERVAL DAY TO SECOND(6))
FROM some_table;
CAST('0'||TIME_FIELDASINTERVALDAYTOSECOND(6))
---------------------------------------------------------------------------
+00 00:00:00.000000
...这几乎是@catcall最初建议的,但这也需要在前面加上'0 '
,否则您将得到一个ORA-01867
. (或者,如果您尝试使用HOUR TO SECOND
,ORA-00963
,即使使用预设的日期值).但是,我猜(虽然只是受过很小的教育,但这只是一个猜测),它正在执行隐式TO_DSINTERVAL
或类似操作,并且我认为我更喜欢使用一个显式的TO_DSINTERVAL
,因此可以肯定发生了什么.不过那可能只是我...
... which is pretty much what @catcall first suggested, but this also needs the '0 '
prepended, or you get an ORA-01867
. (Or, if you try to use HOUR TO SECOND
, ORA-00963
, even with the prepended day value). However, I guess (and it is just a guess, though very slightly educated) that it's doing an implicit TO_DSINTERVAL
or similar, and I think I'd prefer to use an explicit one so I was sure what's happening. That might just be me, though...
使用样本数据我也得到一个ORA-01867
,但这是由空值引起的.您可以使用case
在结果中将其保留为空:
Using your sample data I too get an ORA-01867
, but it's caused by the null value. You can use a case
to leave that as null in the result:
SELECT CASE WHEN time_field IS NULL THEN null
ELSE CAST('0 ' || time_field AS INTERVAL DAY TO SECOND(6)) END
FROM some_table;
CASEWHENTIME_FIELDISNULLTHENNULLELSECAST('0'||TIME_FIELDASINTERVALDAYTOSECO
---------------------------------------------------------------------------
+00 10:00:00.000000
+00 12:00:00.000000
+00 15:00:00.000000
+00 17:00:00.000000
+00 20:00:00.000000
6 rows selected.
这篇关于您如何选择"HH24:MI:SSxFF6"格式的varchar2字段作为秒间隔(6)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!