Creating a stored function in SQL to check if a year is a leap year or not

Question

I am not sure if the problem lies in my logic, or incorrect use of syntax :/

Gives the error "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET Answer = "Leap Year"; ELSE SET Answer = "Not a Leap Year";"

DELIMITER $$

DROP FUNCTION IF EXISTS f_Leap_Year; 

CREATE FUNCTION f_Leap_Year(ENTER_YEAR INT)
RETURNS VARCHAR(30)

BEGIN

  DECLARE Answer VARCHAR(30);
  DECLARE ENTER_YEAR INTEGER;

  If (Enter_Year % 400 = 0
  OR (Enter_Year % 4 =0 and not Enter_Year % 100 = 0))
  SET Answer = "Leap Year";
  ELSE SET Answer = "Not a Leap Year";

  RETURN Answer;

END $$

The name of the variable needs to start with an @ sign. – Ole EH Dufour Mar 18 '18 at 16:25 — Ole EH Dufour, Mar 18 '18 at 16:25
See https://stackoverflow.com/a/11754790/4180382 – Ole EH Dufour Mar 18 '18 at 16:27 — Ole EH Dufour, Mar 18 '18 at 16:27

score 4 · Answer 1 · answered Mar 18 '18 at 16:26

4

This is a redundant function. Here's why...

SET @year = '2016';

SELECT CASE WHEN DATE_FORMAT(CONCAT(@year,'-03-01') - INTERVAL 1 DAY,'%d') = 29 THEN 1 ELSE 0 END is_leap;
+---------+
| is_leap |
+---------+
|       1 |
+---------+

answered Mar 18 '18 at 16:26

Strawberry

33,750
13
40
57

score 1 · Answer 2 · answered Mar 18 '18 at 17:02

This answer is based on Strawberry's answer.
it is also possible without using - INTERVAL 1 DAY because DATE_FORMAT('2015-02-29', '%d') will generate a NULL value when it is a invalid date.

Queries

  SET @year = '2015';
  SELECT CASE WHEN DATE_FORMAT(CONCAT(@year, '-02-29'), "%d") IS NOT NULL THEN 1 ELSE 0 END is_leap;

  SET @year = '2016';
  SELECT CASE WHEN DATE_FORMAT(CONCAT(@year, '-02-29'), "%d") IS NOT NULL THEN 1 ELSE 0 END is_leap;

Results

+---------+
| is_leap |
+---------+
|       0 |
+---------+

+---------+
| is_leap |
+---------+
|       1 |
+---------+

demo

http://rextester.com/NQB35930

score 0 · Answer 3 · answered Mar 18 '18 at 21:48

Thanks for your responses. In fact, I think the problem was that I was missing THEN from each of the SET statements, and a DETERMINISTIC; the code below worked.

DROP FUNCTION IF EXISTS LeapYear;

CREATE FUNCTION LeapYear(EnterYear INT) RETURNS VARCHAR(30)
  DETERMINISTIC
BEGIN

  DECLARE answer VARCHAR(35);

  IF (Enter_Year % 400 = 0
  OR (Enter_Year % 4 =0 and not Enter_Year % 100 = 0)) THEN 
  SET answer = 'Leap year';
  ELSE
  SET answer = 'Not a leap year';
  END IF;

 RETURN (answer);
END

But now I have a new problem - while I can create the function and it shows up as existing within the database, when I try to use it, I get the following error:

MariaDB [my_dbname]> select leapyear(1976)$$
ERROR 1054 (42S22): Unknown column 'Enter_Year' in 'field list'

It seems to think I am trying to select a column rather than calling a function - does anyone know why this might be happening?

Creating a stored function in SQL to check if a year is a leap year or not

3 Answers3