skip to Main Content

First Attempt –

CREATE FUNCTION rental_quarter (rental_date TIMESTAMP)
RETURNS VARCHAR(7)
BEGIN 
 IF MONTH(rental_date) BETWEEN 1 AND 3 THEN RETURN YEAR(rental_date)+ ”Q1”;
 ELSE IF MONTH(rental_date) BETWEEN 4 AND 6 THEN RETURN YEAR(rental_date)+ ”Q2” ; 
 ELSE IF MONTH(rental_date) BETWEEN 7 AND 9 THEN RETURN YEAR(rental_date)+ ”Q3”;
 ELSE IF MONTH(rental_date) BETWEEN 10 AND 12 THEN RETURN YEAR(rental_date)+ ”Q4”;
 ELSE RETURN NULL ;
END IF;
END;

Latest try Code and Error

EDIT Suggested fix error #1

I am new to sql and have been stuck on this a few hours . I am trying to create a rental_quarter(rental_date) function that turns the time stamped date format (without timezone) into year + quarter. I have no Idea what I am doing wrong. I have tried using AS, DECLARE, SET AS. I’ve done every variation of semicolons. It has to be a user defined function to meet project requirements. Please help.

2

Answers


  1. This is with minimum changes I think:

    CREATE FUNCTION rental_quarter (rental_date TIMESTAMP)
    RETURNS VARCHAR(7) AS
    $BODY$
    BEGIN 
     IF MONTH(rental_date) BETWEEN 1 AND 3 THEN RETURN YEAR(rental_date)+ ”Q1”;
     ELSIF MONTH(rental_date) BETWEEN 4 AND 6 THEN RETURN YEAR(rental_date)+ ”Q2”;  
     ELSIF MONTH(rental_date) BETWEEN 7 AND 9 THEN RETURN YEAR(rental_date)+ ”Q3”;
     ELSIF MONTH(rental_date) BETWEEN 10 AND 12 THEN RETURN YEAR(rental_date)+ ”Q4”;
     ELSE RETURN NULL ;
     END IF;
    END;
    $BODY$
    LANGUAGE PLPGSQL;
    
    Login or Signup to reply.
  2. There is already a function for this, to_char(), you don’t have write one yourself:

    SELECT to_char('2023-08-01'::timestamp, 'YYYY"Q"Q');
    

    Result: 2023Q3

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search