date to day function - MySQL - help needed

Question

I'm trying to create a MySQL function where I supply the date column and I get the day back in a new column using the below function. This is what I have. The select statement works fine outside the function - I tested it there. Can anyone help get this function going. 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

mantigatos · Answer 1 · 2012-03-17T15:17:24.887

Take advantage of built-in function DAYNAME(date)

UPD.

If you want to assign some value, returned by select statement, to variable yourvariable, you should use select ... into yourvariable

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 INTO DayName1;


RETURN DayName1;

END

Thanks - thats great. I'd like to get this working though as a mater of learning what is wrong — Kes, Mar 17 '12 at 15:05

score 0 · Answer 2 · answered Mar 17 '12 at 15:04

0

Why reinvent the wheel?

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek

answered Mar 17 '12 at 15:04

Straseus

478
1
4
14

Good point. Not why, rather I did'nt know about the function. I'd like to get what i've got going as a matter of understanding why it does not work. Thanks – Kes Mar 17 '12 at 15:17

date to day function - MySQL - help needed

2 Answers2