## Create Function

User, date | Message |
---|---|

Written by kez
2 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 kez
2 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 kez
2 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
2 years ago
4967 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' ) |

Written by kez
2 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
2 years ago
4967 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
2 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 kez
2 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 kez
2 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. |