Create Function

User, date Message
Written by kez
1 year ago
Category: General
20 posts since Tue, 21 Sep 10 		I'd be very grateful if someone would provide an simple example function and an exact step by step guide as to how to get this going in HeidiSQL.
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		Hi,
I'm trying to create a function where I supply the yyyy-mm-dd column and I can produce a column with the corresponding day.
This is what I have. Can anyone help.
Thanks


CREATE FUNCTION `GetWeekDayNameOfDate`(`Date1` date)
RETURNS VARCHAR(50)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE DayName1 varchar(50)

SELECT
CASE DATE_FORMAT(Date1 , '%w' )
WHEN 0 THEN 'Sunday'
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday' else null end;


RETURN DayName1;

END
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		I found out there is a dayname(date) function built in. I'd like to get the above posted function going though as a matter of finding out what is wrong with it. Thanks :-)
Written by ansgar
1 year ago
3950 posts since Fri, 07 Apr 06 		That DECLARE syntax is somehow wrong, and you do not set the value for DayName1. You just need to RETURN the CASE command (instead of SELECTing it):

RETURN CASE DATE_FORMAT(Date1 , '%w' )
WHEN 0 THEN 'Sunday'
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday' else null end;
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		Thanks Anse for the recommended changes.

I again tried some variations of the below. Still need some help

CREATE FUNCTION `GetWeekDayNameOfDate`(`Date1` DATE)
RETURNS VARCHAR(50)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'a'
BEGIN

DECLARE DayName1 varchar(50)

RETURN CASE DATE_FORMAT(Date1, '%w' )
WHEN 0 THEN 'Sunday'
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday' else null end as DayName1;

END
Written by ansgar
1 year ago
3950 posts since Fri, 07 Apr 06 		Where's the problem now?

Looks like you want to use some "DayName1" variable, forcing it to be VARCHAR(50). This is not required as you already configure the function's return type.
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		hi,

OK taken the "DayName1" VARCHAR(50) line out and tried lots of variations on what I have and nothing works. Is there a good tutorial on functions online anywhere ?

thank you
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		Thank you Anse :-)

Here is the working code.

Does anyone know a good tutorial on function structure ?

CREATE DEFINER=`root`@`localhost` FUNCTION `DATETOWEEKDAY`(`Date1` DATE)
RETURNS varchar(50)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN



RETURN CASE DATE_FORMAT(Date1, '%w' )
WHEN 0 THEN 'Sunday'
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday' else null end;


END
Written by kez
1 year ago
20 posts since Tue, 21 Sep 10 		yes ... varchar(9) should be 9 max
 

Please login to leave a reply, or register at first.