ORA-01843-不是有效的月份-仅适用于少数日期格式

Question

使用Toad for Oracle。

我的初始表格中有以下2020-07-01T00:00:00格式的日期。我使用以下代码从每个日期删除从T开始的所有内容：

SELECT NVL(SUBSTR(date_column_0, 0, INSTR(date_column_0, 'T')-1), date_column_0) as date_column_0 FROM original_table;

我在另一个测试表中有这些日期，其中date_column类型是VARCHAR:

date_column
2020-07-01
2020-08-01
2020-09-01

我需要将这些日期转换为YYYYMM格式(即202007(到目前为止，我已经尝试过

1(

select to_char(cast(date_column as date, 'YYYYMM')) from test_table;
select to_char(to_date(date_column, 'YYYYMM')) from test_table

这两者都产生误差CCD_ 3。

2(

select to_char(cast(column_6 as date), 'YYYYMM') from test_table;
select to_char(cast(column_6 as date), 'YYYY/MM/DD') from test_table;

两者都产生误差CCD_ 4。

---

由于某种原因，

select to_char(cast(date_column as date, 'YYYY/MM/DD')) from test_table;
select to_char(to_date(date_column, 'YYYY/MM/DD')) from test_table

两者都以01-JUL-20的格式工作并显示日期，这显然是我不想要的。如果我从dual中选择类似的日期格式，它也可以完美工作(下面的代码产生"202004"(：

select to_char(cast(sysdate as date),'YYYYMM') from dual;
---> 202004

---

编辑日期：2020年4月23日我想我早就想明白了我必须首先从VARCHAR"date"中选择一个SUBSTRING，以便它与我要将其转换为的日期格式相匹配。因此，

SELECT ((SUBSTR(date_column, 9, 2)  || SUBSTR(date_column, 6,2)  || SUBSTR(date_column, 1,4))) FROM test_table;

是SUBSTRING命令，用于选择DDMMYYYY格式的日期。和以下命令

SELECT to_char(cast(SUBSTR(date_column, 9, 2)  || SUBSTR(date_column, 6,2)  || SUBSTR(date_column, 1,4) as date, 'DD-MM-YYYY')) FROM test_table;

是将其转换为日期的命令。

Answer 1

我写了一些简单的代码，从您的专栏中提取月份和年份：

select lpad(extract(month from to_date('2008-09-01' , 'YYYY-MM-DD')),2,'0') 
||lpad(extract(year from to_date('2008-09-01' , 'YYYY-MM-DD')),4,'0')  
from dual;

如果你想从中创建日期，你可以简单地这样做：

select to_date(lpad(extract(month from to_date('2008-09-01' , 'YYYY-MM-DD')),2,'0')
||lpad(extract(year from to_date('2008-09-01' , 'YYYY-MM-DD')),4,'0')
,'MMYYYY')  
from dual;

所以在你的例子中是这样的：

select to_date(lpad(extract(month from to_date(date_column , 'YYYY-MM-DD')),2,'0')
||lpad(extract(year from to_date(date_column  , 'YYYY-MM-DD')),4,'0')
,'MMYYYY')  
from test_table;

Answer 2

如果这些是字符串，并且您想要一个字符串，那么：

select substr(date, 1, 4) || substr(date, 6, 2) as yyyymm

实际上，您可以在两个表上都使用此功能。

如果您想将原始表格中的日期与其他日期相匹配，请使用：

select . . .
from original o join
testtable tt
on o.datecol >= to_date(tt.datecol, 'YYYY-MM-DD') and
o.datecol < to_date(tt.datecol, 'YYYY-MM-DD') + interval '1' month

Answer 3

如果列的数据类型为TIMESTAMP，请尝试此列！

SELECT to_char(date_column, 'YYYYMM') FROM table_name

如果数据类型是varchar，那么试试这个，

SELECT TO_CHAR(CAST(column_name as TIMESTAMP), 'YYYYMM') FROM table_name