CODEWITHSUNDEEP
sqloracle-databasefunction
Pointer
Pointer

Reputation: 2186

Date not valid for month specified - ORA-01839

Is it possible inside function put execption to avoid a mistake if put wrong date?

Select F_RETURN_DAY_NAME(2021,6,30) from dual;     --WORK (Jun have 30 days)
Select F_RETURN_DAY_NAME(2021,6,31) from dual;     --DONT WORK



create or replace Function F_RETURN_DAY_NAME
       ( 
         p_year IN number,
         p_month IN number,
         p_day IN number
       )
       RETURN varchar2
    IS
       v_return varchar2(100);
    
    BEGIN
    
         return substr(to_char(TO_DATE(p_year || '-' || p_month || '-' || p_day, 'YYYY-MM-DD'),'DY'),0,3) ; 
    
    END;

Upvotes: 1

Views: 567

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

As you're on a recent version you can use the default ... on conversion error clause for to_date():

create or replace Function F_RETURN_DAY_NAME
( 
  p_year IN number,
  p_month IN number,
  p_day IN number
)
  RETURN varchar2
IS
BEGIN
  return coalesce(
    to_char(to_date(p_year || '-' || p_month || '-' || p_day
      default null on conversion error, 'YYYY-MM-DD'),'DY'),
    'Wrong day') ; 
END;
/

which gets:

Select F_RETURN_DAY_NAME(2021,6,30) from dual;

F_RETURN_DAY_NAME(2021,6,30)
----------------------------
WED

and

Select F_RETURN_DAY_NAME(2021,6,31) from dual;

F_RETURN_DAY_NAME(2021,6,31)
----------------------------
Wrong day

db<>fiddle

I've removed the local variable you weren't using; and the substring - DY gives you the abbreviated day name. In English that's always three characters anyway; in other languages it can be longer, but as that's to avoid ambiguity only taking the first three characters is going to cause confusion. If you want to ensure the day abbreviation is always a specific language, regardless of the user's session settings, then you can supply another argument to to_char():

  return coalesce(
    to_char(to_date(p_year || '-' || p_month || '-' || p_day
      default null on conversion error, 'YYYY-MM-DD'),
      'DY',
      'NLS_DATE_LANGUAGE=ENGLISH'),
    'Wrong day') ;

db<>fiddle

The concern is that the function may also be called on null input, which is not invalid; the function should indeed return null in that case (guessing here, but that is what most use cases would require, I think), rather than returning 'Wrong day'

You can check for all values being null before attempting conversion:

...
  if p_year is null and p_month is null and p_day is null then
    return null;
  end if;

  return coalesce(
...

db<>fiddle

You'll need to decide how you want to handle only one or two arguments being null; at the moment that (or month 13, or year 10000) will all return 'Wrong day'. If you want any null argument to return null then do the same thing but with 'or' instead of 'and', for example. And you might want a more generic return value on error - 'Invalid date', say, which I've used in the latest Fiddle.

Upvotes: 2

Related Questions