0

I have created a function which need input as month(11) and year(2018) and it will return the first Sunday of the next month.

Create or replace function get_effectivedate (par_Month in int,Par_Year in int);

Return date

Is 

    startDate varchar;
    edate date;

    begin
    startDate := par_Month+Par_Year;
    edate     := select  next_day(add_months(trunc(startDate, 'MM'), 1), 'Sunday') as EffectiveDate from dual; 

     return edate;
     end;

This function is prompting me while running and it is throwing some errors on compilation.

Error:

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

Function GET_EFFECTIVEDATE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

Function GET_EFFECTIVEDATE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
7/1       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     := . ( @ % ; not null range with default character The symbol ";" was substituted for "BEGIN" to continue. 
9/14      PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an alternat
11/2      PLS-00103: Encountered the symbol "RETURN" 
Errors: check compiler log

enter image description here

Improve this question
2
  • 1
    It's usually helpful to say which errors you get (and what prompts you are getting, if they are unexpected). Commented Jul 24, 2018 at 14:17
  • Alex, i have added the error while tryign to run the function. Commented Jul 24, 2018 at 14:26

2 Answers 2

Reset to default
2

You have several issues:

  • you have a trailing semicolon on the create ... line;
  • you are using a substitution variable (denoted by &) - which you are being prompted for;
  • you are using int instead of number for the argument types (not really an error, but using native types is usuually better);
  • your startdate local variable is declared as varchar instead of varchar2, which works but is discouraged, but either way you have to specify a laximum length for the string (i.e. startdate varchar2(6););
  • you are adding the two numbers together with +, and putting the result of that (i.e. 2029) into a string variable;
  • you're using that string variable - if you ignore the & - as a number, not a date, but then trunc won't work;
  • if you have the first day of the month already then trunc is pointless anyway.

You can do this quite simply as:

create or replace function get_effectivedate (par_month in number, par_year in number)
return date
is 
begin
  return next_day(
    add_months(
      -- converts the two number to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM'),
      1),
    'Sunday');
end;
/

then call it as:

select get_effectivedate(11, 2018) from dual;

GET_EFFECT
----------
2018-12-02

or for all months this year via a CTE:

with cte (year, month) as (
  select 2018, level from dual connect by level <= 12
)
select year, month, get_effectivedate(month, year)
from cte;

      YEAR      MONTH GET_EFFECT
---------- ---------- ----------
      2018          1 2018-02-04
      2018          2 2018-03-04
      2018          3 2018-04-08
      2018          4 2018-05-06
      2018          5 2018-06-03
      2018          6 2018-07-08
      2018          7 2018-08-05
      2018          8 2018-09-02
      2018          9 2018-10-07
      2018         10 2018-11-04
      2018         11 2018-12-02
      2018         12 2019-01-06

Converting both of the arguments to strings explicitly lets you control leading zeros, so you can be sure that passing in say 1 and 234 will convert to strings '01' and '0234', so when they are concatenated they form '023401', to match the format model.

If you don't specify a day number explicitly then to_date() gives you the first day of the month.

The function above gets the second Sunday if the first of the month is itself a Sunday, which you said in comments is what you actually want. (Notice 2018-07-08). If you did actually just want the first Sunday with no further adjustment you do either:

  return next_day(
    add_months(
      -- converts the two numbers to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM'),
      1) - 1, -- find first day of next month, then go back one day to last day of this month
    'Sunday'); -- find next Sunday from that day

or more simply:

  return next_day(
    last_day(
      -- converts the two numbers to a single string, and then converts that to a date
      to_date(to_char(par_year, 'FM0000') || to_char(par_month, 'FM00'), 'YYYYMM')
      ), -- find last day of this month
    'Sunday'); -- find next Sunday from that day

either of which givesL

      YEAR      MONTH GET_EFFECT
---------- ---------- ----------
      2018          1 2018-02-04
      2018          2 2018-03-04
      2018          3 2018-04-01
      2018          4 2018-05-06
      2018          5 2018-06-03
      2018          6 2018-07-01
      2018          7 2018-08-05
      2018          8 2018-09-02
      2018          9 2018-10-07
      2018         10 2018-11-04
      2018         11 2018-12-02
      2018         12 2019-01-06
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

i need to use this logic " next_day(add_months(trunc(startDate, 'MM'), 1), 'Sunday')" in the function as well
@author4 - this is doing next_day(add_months(..., 1), 'Sunday'). I've left the trunc(.., 'MM') out because it isn't doing anything useful.
if the next month 1st day is sunday then it should consider 2nd sunday or else first sunday
@author4 - that isn't what you asked for in this question, but it is actually what this does, accidentally - because I forgot to go back a day before calling next_day. You've asked for several variations in previous questions and any of those can be adapted to work in a function in the same way.
@author4 : You should explore a bit and try to find solution to the problem rather than keep coming up with new changes in your requirement which wasn't mentioned in the question. Alex has given you all the required information for you to be able to solve your problem. Some effort is also expected from your side as it is your new assignment.
|
0

You want to call startDate variable so remove ampersand sign,

& is s a sign for prompting user's input. Change your line in code to:

edate     := select  next_day(add_months(trunc(startDate, 'MM'), 1), 'Sunday') as EffectiveDate from dual;

&value is a user entered parameter

Improve this answer

2 Comments

Okay, thak you. Is the other syntax and logic is fine?
i tried removing it and still getting some errors. also, i have updated the question please check

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.