-7

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.

KB_Wonderland
  • 1
  • 2
  • 1
    1) The code shown above and the one in the picture is difference. 2) Post all text here as text, not as links to images. – deceze Jul 02 '23 at 20:15
  • 1
    Documentation is your friend: 1) [plpgsql structure](https://www.postgresql.org/docs/current/plpgsql-structure.html) 2) Anonymous function [DO](https://www.postgresql.org/docs/current/sql-do.html) – Adrian Klaver Jul 02 '23 at 20:17
  • I clearly stated they were my starting attempt and ending attempt. – KB_Wonderland Jul 02 '23 at 20:18
  • 1
    To repeat comment from @deceze **DO NOT** post textual information as images. Copy and paste as text. – Adrian Klaver Jul 02 '23 at 20:53

2 Answers2

0

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

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
-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;
Bjarni Ragnarsson
  • 1,731
  • 1
  • 6
  • 8