distal-attribute
distal-attribute
distal-attribute
distal-attribute

Create Function

User, date Message
Written by kezMoney, Euro
3 years ago
Category: General
21 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 kezMoney, Euro
3 years ago
21 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 kezMoney, Euro
3 years ago
21 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
3 years ago
5023 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 kezMoney, Euro
3 years ago
21 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
3 years ago
5023 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 kezMoney, Euro
3 years ago
21 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 kezMoney, Euro
3 years ago
21 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 kezMoney, Euro
3 years ago
21 posts since Tue, 21 Sep 10
yes ... varchar(9) should be 9 max
 

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