asked on

case in a where clause using to_date function

Hello there,

I've working in the following script for the whole day, but I'm getting the following error:

ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:

Basically, I have to extract a date from a char type field called 'text' if it meets some conditions: if name field starts with 'P' otherwise, it should take another field which is date type.

SELECT
supplier_id, name,SUM(net_amount) Net_Amount
FROM supplier_table
WHERE
(CASE
WHEN name like 'A%' and SUBSTR( text, 4 , 1 ) = '/'
THEN to_date(SUBSTR(text,5,8),'MM-DD-YY') -- here, this field char type format 12-30-18
WHEN name like 'B%' SUBSTR( text, 4 , 1 ) = '-')
THEN to_date(SUBSTR(text,4,8),'MM-DD-YY') -- here, this field char type format 12-30-18
WHEN name like '%C%'
THEN invoice_date -- here, this field date type format 30-DEC-18
end) between TO_DATE('&START_DATE', 'mm/dd/yy') and TO_DATE('&END_DATE','mm/dd/yy')
group by supplier_id, name
order by name

I'm so confused about these type of date formats.
My database save date type with this format 01-JAN-18

Thanks for help.

JS

slightwv (䄆 Netminder)

>>My database save date type with this format 01-JAN-18

Oracle saves the date in "date" format. It doesn't have a format until queried. Then Oracle defaults the value based on DD-MON-YY unless you set NLS_DATE_FORMAT or use TO_CHAR.

As far as the error: Have you narrowed down what part of the query is causing the error?

The string MUST MATCH the format mask. Either your format mask is bad or some of your data is bad.

>> -- here, this field date type format 30-DEC-18

Remember: Oracle dates don't have a format. If you don't provide a TO_DATE format Oracle tries to do an implicit conversion. Unless invoice_date is an actual DATE data type.

slightwv (䄆 Netminder)

You also have a syntax error in the CASE statement. It won't run as posted.

You also don't really need the case statement. Oracle is pretty good about converting strings to dates. The '-' and '/' really doesn't matter:

SQL> select to_date('01/02/18','MM-DD-YY') from dual;

TO_DATE('
---------
02-JAN-18

SQL> select to_date('01/02/18','MM/DD/YY') from dual;

TO_DATE('
---------
02-JAN-18

Open in new window

You still need to confirm the data matches the format mask.

Can you create a function? If so, you can look for the bad data.

Assuming the text fields offsets still apply, otherwise the CASE statement gets simpler.

create or replace function valid_date(p_supplier_id in varchar2) return char
is
	junk date;
begin
	select case
     	WHEN name like 'A%' THEN to_date(SUBSTR(text,5,8),'MM-DD-YY')
      	WHEN name like 'B%' THEN to_date(SUBSTR(text,4,8),'MM-DD-YY')
      	WHEN name like '%C%' THEN invoice_date
	end into junk
FROM supplier_table
where supplier_id=p_supplier_id;
	return 'Y';
exception when others then 
return 'N';
end;
/

show errors

Open in new window

Then you can look for bad data with:
select supplier_id, valid_date(supplier_id) from supplier_table;

PatHartman

Any chance you can solve the real problem which is that dates are stored as a string along with other stuff in it?

PortletPaul

If this data is stored as a string, then there is always the (strong?) possibility that it will NOT convert to a date, simply because a string allows any characters in any order. If you attempt to use to_date() on a string that cannot be converted it will produce an error.

IF you data is a string like this '01-JAN-18' then you may want to build a where clause to exclude (or in the inverse, find) exceptions

e.g.
exclude where not JAN FEB ... DEC
exclude where first 2 chars not digits between 00 and 31
exclude where last 2 chars are not digits between 00 and 18 (?)

sorry to cut this short, i'll try to get back soon.

PortletPaul

The syntax for TO_DATE function is:

TO_DATE( string1 [, format_mask] [, nls_language] )

e.g. for your data

to_date('01-JAN-18','dd-mon-yy')

.... you can use upper or lower case in the mask

e.g.

CREATE TABLE TABLE1
        (DATESTRING varchar2(9))
    ;
    
    INSERT ALL
        INTO Table1 ("DATESTRING")
             VALUES ('01-JAN-18')
        INTO Table1 ("DATESTRING")
             VALUES ('33-GAN-19')
        INTO Table1 ("DATESTRING")
             VALUES ('jan-01-18')
    SELECT * FROM dual
    ;


    select to_date(datestring,'dd-MON-yy')
    from table1
    where substr(datestring,1,2) between '01' and '31'
    and upper(substr(datestring,4,3)) in ('JAN','FEB','MAR','DEC')
    and substr(datestring,8,2) between '01' and '99'


    | TO_DATE(DATESTRING,'DD-MON-YY') |
    |---------------------------------|
    |            2018-01-01T00:00:00Z |

Open in new window

but of course this will still fail on 30th of February, or the 31st of any month that only has 30 days.

by the way, there might be a regular expression approach to this instead. I think you need to consider what is best based on your data, of which we know almost nothing.

jtrifts

Dude...you're trying to Cast a 4 & 5 character string into a date using a format mask larger than that. It makes no sense, and will clearly result in an invalid date/invalid.month error.

Can you provide an example with the actual data you're trying to convert? It will be clearer what the solution will be.

slightwv (䄆 Netminder)

>>you're trying to Cast a 4 & 5 character string into a date

I see an 8 character string. What are you seeing?

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E

jtrifts

I think I lost my mind for a sec there! Please ignore my momentary madness.

So... I did notice a missing AND following your B%...

But most likely what is happening is that you are either getting the order of the date parts wrong (perhaps your data is actually dd-mm-yyyy and you have assumed it is mm-dd-yyyy)... Or there is a value in the "month field" which is either >12 or contains a non numeric character.

Jerry Seinfield

ASKER

About date format I have this in Oracle SQL developer:

NLS_DATE_FORMAT DD-MON-RR

Yes, I have to extract a date from a string...you're right, many chances of errors.
this a real example:

case
when
(SUBSTR( string, 4 , 1 ) = '/' OR SUBSTR( string, 4 , 1 ) = '-' then TO_DATE(SUBSTR(string,5,8),'MM-DD-YY')
else
invoicedate
end

i.e. ABC/12-30-18
BCD/10-20-18
CDE-11-25-18

i need to extract a string after 3 characters (always 3 characters) / or -, and convert it in a date;otherwise use invoicedate field. And after this use the result date in a where clause.

ASKER CERTIFIED SOLUTION

jtrifts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jerry Seinfield

ASKER

Great idea!
Thanks for the advise. I'll check my data.

Jerry Seinfield

ASKER

thanks PortletPaul, very helpful.