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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 21:31:28  来源:igfitidea点击:

Oracle to_date function. Mask needed

oracleformatmaskto-date

提问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 OZisn'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