CODEWITHSUNDEEP
sqloracle-databasetoad
jeppoo1
jeppoo1

Reputation: 698

ORA-01843 - not a valid month - only working with a few date formats

Using Toad for Oracle.

My initial table has dates in the format of the following 2020-07-01T00:00:00. I'm using the following code to remove everything starting from T from every date:

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

These dates I have in another test table, where date_column type is VARCHAR:

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

I need to convert those dates to YYYYMM format (i.e. 202007). So far I have tried

1) 

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

Both of these yield the error ORA-01843 - not a valid month.

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;

Both yield the error ORA-01861: literal does not match format string.

For some reason,

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

both work and shows the dates in the format 01-JUL-20, which I obviously do not want. If I select the similar date format from dual, it also works perfectly (the code below yields `202004``):

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

EDIT 23.4.2020 I think I figured it out though should have figured it out much earlier... I have to first select a SUBSTRING from the VARCHAR "date" so that it matches the date format I want to convert it to. So, 

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

is the SUBSTRING command to select the date in the format DDMMYYYY. And the following command

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;

is the command to convert it to date.

Upvotes: 0

Views: 1119

Answers (3)

Harisudha
Harisudha

Reputation: 597

If the datatype of the column is TIMESTAMP, then try this one!

SELECT to_char(date_column, 'YYYYMM') FROM table_name

If the datatype is varchar, try out this,

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

If these are strings and you want a string, how about:

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

You can actually use this on both tables.

If you want to match the dates in the original table to the other, then use:

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

Upvotes: 1

Bartosz
Bartosz

Reputation: 1820

I wrote some simple code that extracts month and year from your column:

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;

if you would like to create date from this, you could do simply like:

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;

So in your example it would be like that:

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;

Upvotes: 2

Related Questions