英文:
Can someone help me resolve an sql syntax error on pgadmin while creating a function?
问题
第一次尝试 -```sqlCREATE FUNCTION rental_quarter (rental_date TIMESTAMP)RETURNS VARCHAR(7)BEGINIF 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;
最新尝试 代码和错误
我对SQL很陌生，已经在这上面卡了几个小时了。我正在尝试创建一个rental_quarter(rental_date)函数，将时间戳日期格式（没有时区）转换为年份+季度。我完全不知道我做错了什么。我尝试过使用AS、DECLARE、SET AS。我已经尝试了各种分号的变化。它必须是一个用户定义的函数，以满足项目要求。请帮忙。
<details><summary>英文:</summary>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](https://i.stack.imgur.com/Bw3xX.png)[EDIT Suggested fix error #1][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.[1]: https://i.stack.imgur.com/AdodW.png</details># 答案1**得分**: 0已经有一个用于此目的的函数，to_char()，您不必自己编写一个：SELECT to_char('2023-08-01'::timestamp, 'YYYY"Q"Q');结果：2023Q3<details><summary>英文:</summary>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</details># 答案2**得分**: -1这是我认为需要最少更改的版本：```sqlCREATE FUNCTION rental_quarter (rental_date TIMESTAMP)RETURNS VARCHAR(7) AS$BODY$BEGINIF 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;
英文:
This is with minimum changes I think:
CREATE FUNCTION rental_quarter (rental_date TIMESTAMP)RETURNS VARCHAR(7) AS$BODY$BEGINIF 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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论