Create Function

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.

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

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 :-)

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;

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

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.

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

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

yes ... varchar(9) should be 9 max