Oracle to_date 函数。需要口罩
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3724665/
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 to_date function. Mask needed
提问by Andrey Khataev
I have string of date from xml file of such kind: '2010-09-09T22:33:44.OZ'
我有来自此类 xml 文件的日期字符串:'2010-09-09T22:33:44.OZ'
I need to extract only date and time. I want to ignore symbol T and .OZ (time zone). Which mask I should use? Thanks in advance
我只需要提取日期和时间。我想忽略符号 T 和 .OZ(时区)。我应该使用哪种面膜?提前致谢
回答by Jeffrey Kemp
select TO_DATE('2010-09-09T22:33:44.OZ'
,'YYYY-MM-DD"T"HH24:MI:SS".OZ"')
from dual;
9/09/2010 10:33:44 PM
回答by Alex Poole
If the timezone information is needed:
如果需要时区信息:
select to_timestamp_tz('2010-09-09T22:33:44.GMT','YYYY-MM-DD"T"HH24:MI:SS.TZR')
from dual;
09-SEP-10 22.33.44.000000000 GMT
But OZ
isn't a recognised timezone abbreviation, so you'd need to do some pre-conversion of that to something that is.
但OZ
不是公认的时区缩写,因此您需要将其预先转换为实际的时区缩写。
If you want to just ignore that part, and it's fixed, you can do what @Jeffrey Kemp said:
如果您只想忽略该部分,并且已修复,您可以按照@Jeffrey Kemp 所说的进行操作:
select to_date('2010-09-09T22:33:44.OZ','YYYY-MM-DD"T"HH24:MI:SS."OZ"')
from dual;
09/09/2010 22:33:44 -- assuming your NLS_DATE_FORMAT is DD/MM/YYYY HH24:MI:SS
If you want to ignore it but it isn't fixed then you'll need to trim it off first, something like (using a bind variable here for brevity):
如果你想忽略它但它不是固定的,那么你需要先修剪它,比如(为了简洁起见,在这里使用绑定变量):
var input varchar2(32);
exec :input := '2010-09-09T22:33:44.OZ';
select to_date(substr(:input,1,instr(:input,'.') - 1),'YYYY-MM-DD"T"HH24:MI:SS')
from dual;
09/09/2010 22:33:44