CODEWITHSUNDEEP
sqloracle-databaseplsql
Abenamor
Abenamor

Reputation: 318

Find the last day of the semestre of a given date

I would like to implement a plsql function that takes a date as parameter and return the last day of the semestre of the parameter date.

function find_lastday_of_semetre(p_datdeb IN date := current_date)
      return date IS
  begin
      declare
          semestreLastDay date;
      begin

            -- semestreLastDay = the last day of the semestre of the p_datdeb
          return semestreLastDay ;
      end;
  end find_lastday_of_semetre;

For example :

I figure out how to get the last day of the month, the quarter and the year based in a date param but I couldn't figure out how to get the last day of the semestre.

Get the last day of the quarter

semestreLastDay := TRUNC(ADD_MONTHS(p_datdeb, 3), 'Q') - 1

Get the last day of the month

semestreLastDay := LAST_DAY(p_datdeb)

Get the last day of the year

semestreLastDay := ADD_MONTHS(TRUNC(p_datdeb, 'YEAR'), 12) - 1

Thanks

Upvotes: 0

Views: 323

Answers (2)

blackbishop
blackbishop

Reputation: 32680

You can extract the month and check whether it's greater than 6 or not and return date from parts : year from the input date and 06-30 or 12-31 :

return to_date(CASE
                 WHEN extract(month from p_datdeb) <= 6 THEN to_char(p_datdeb, 'yyyy') || '-' || '06-30'
                 ELSE to_char(p_datdeb, 'yyyy') || '-' || '12-31'
               END, 'yyyy-MM-dd')

Upvotes: 1

user5683823
user5683823

Reputation:

I don't see why you put the code for the function inside a nested block. I assume it was for no reason (just lack of knowledge).

You don't need a local variable, either - you can put the computation directly in the return statement, as I show below.

I use TRUNC(date_variable, 'YEAR') to truncate to the beginning of the year, then I subtract one day, and I use ADD_MONTHS to add either six or 12 months to the result, depending on whether the month component of the input date is <= 6 or > 6.

I also correct the spelling - you have "semetre" (with a missing "s") in your code. Especially bad since "semestre" is the main concept in your task.

So - here is a minimalist way to write your function:

create or replace function 
    find_lastday_of_semestre(p_datdeb IN date := current_date)
    return date
as
begin
    return add_months(trunc(p_datdeb, 'year') - 1, 
               case when extract(month from p_datdeb) <= 6 then 6 else 12 end);
end find_lastday_of_semestre;
/

Upvotes: 1

Related Questions