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

Create Function

kez posted 3 years ago in General
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.
kez posted 3 years ago
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
kez posted 3 years ago
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 :-)
ansgar posted 3 years ago
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;

kez posted 3 years ago
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
ansgar posted 3 years ago
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.
kez posted 3 years ago
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
kez posted 3 years ago
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
kez posted 3 years ago
yes ... varchar(9) should be 9 max

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