3

I have an problem in MySQL stored procedure

table tbl_teachers:

id           dat_teacher_doj   fk_int_dept_id

1            1982-01-10          1
2            1979-09-01          1
3            1981-10-13          1

here i need to create an stored procedure to find out the joining date of teachers and if it is a Monday it should display Monday else it should display “Weekday” ? i need the answer like:

call check-date (1982-01-10) ->day weekday

Mureinik
  • 297,002
  • 52
  • 306
  • 350
EjM
  • 103
  • 11

2 Answers2

2

You don't need a stored procedure for this. A simple case expression around a dayofweek call should do the trick:

SELECT id, dat_teacher_doj,
       CASE DAYOFWEEK(dat_teacher_doj) WHEN 2 THEN 'Monday' ELSE 'Weekday' END
FROM   tbl_teachers
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • can u tell me , how can i do this with stored procedure... because i wish to get the answer like: call check-date (1982-01-10) ->day weekday – EjM Aug 02 '15 at 07:39
  • plz help me to do this. – EjM Aug 02 '15 at 08:33
1

just CASE expression with the help of DAYNAME

SELECT id, dat_teacher_doj,
       CASE DAYNAME(dat_teacher_doj) WHEN 'Monday' THEN 'Monday' ELSE 'Weekday' END
FROM   tbl_teachers

Procedure:this will work in localhost phpmyadmin as well as workbench

CREATE PROCEDURE `GetWeekDay`(IN `paramDate` DATE)
 NO SQL 
SELECT CASE DAYNAME(paramDate) WHEN 'Monday' THEN 'Monday' ELSE 'Weekday' END AS 'WkDy'

Call

CALL `GetWeekDay`('2015-08-03');
wiretext
  • 3,302
  • 14
  • 19
  • can u tell me , how can i do this with stored procedure... because i wish to get the answer like: call check-date (1982-01-10) ->day weekday – EjM Aug 02 '15 at 07:44
  • we are select from DB we don't need output Parameter if we need we can pass `(out paramName datatype)` above link will helpful for you – wiretext Aug 02 '15 at 08:24
  • am not useing the php ...jest mysql ! – EjM Aug 02 '15 at 08:58
  • @ashishjobish sorry for delete i have corrected my answer and reposted – wiretext Aug 02 '15 at 11:37