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'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
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
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
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
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
Please login to leave a reply, or register at first.