oracle 从 DD/MM/YYYY HH:MM:SS 到 YYYYMM 的日期转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27056310/
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
date conversion from DD/MM/YYYY HH:MM:SS to YYYYMM
提问by developer
i want to convert date to some other format.
我想将日期转换为其他格式。
Below is the example 04/03/10 09:00:50.000000000 AMto YYYYMM
以下是04/03/10 09:00:50.000000000 AMYYYYMM的示例
Iam not able to get this , below is the query which i used to convert.
我无法得到这个，下面是我用来转换的查询。
select to_char(to_date('04/03/10 09:00:50.000000000 AM','MM/DD/YYYY HH:MM:SS AM'),'YYYYMM') from table;
Iam getting exception as below
我收到如下异常
ORA-01810: format code appears twice 01810. 00000 - "format code appears twice"
ORA-01810: 格式代码出现两次 01810. 00000 - “格式代码出现两次”
回答by Noel
- Format Code for Minutes is MI, not MM. MM is for months.
- You are using 2-digit year. Better to use RR for this. Even better use 4-digit year.
- TO_DATE doesn't store fractional seconds. You need to use TO_TIMESTAMP and use the FF as format code.
- 分钟的格式代码是 MI，而不是 MM。MM是月。
- 您使用的是 2 位数年份。最好为此使用 RR。最好使用 4 位数年份。
- TO_DATE 不存储小数秒。您需要使用 TO_TIMESTAMP 并使用 FF 作为格式代码。
So, your query would be
所以，你的查询将是
select to_char(to_timestamp('04/03/10 09:00:50.000000000 AM','MM/DD/RR HH:MI:SS.FF9 AM'),'YYYYMM')
from table;
回答by rtbf
To achieve your goal there are many issues to resolve ;) Finally I made this like that:
为了实现您的目标，有很多问题需要解决；) 最后我是这样制作的：
select to_char(
to_timestamp('04/03/10 09:00:50.000000000 AM','MM/DD/YYYY HH:MI:SS.FF9 PM',
'nls_date_language = ENGLISH'),
'YYYYMM') from dual;
