MySQL Procedure DATE Variable Operation

Question

I'm new to MySQL, I've a date variable inside my MySQL procedure and I'm trying to find week start date for that IN date variable. My procedure goes as below,

CREATE PROCEDURE my_proc (IN week_start_num INT, IN my_date DATE)
BEGIN
   DECLARE my_new_date DATE;
   #I know what I'm trying here is wrong
   SET my_new_date=startdate - (INTERVAL WEEKDAY( startdate  ) - week_start_num + IF( WEEKDAY( startdate  ) > week_start_num, 0, 7 ))

   #rest of my codes goes here

END

I know this is wrong 'SET my_new_date =startdate - (INTERVAL WEEKDAY( startdate ) - week_start_num + IF( WEEKDAY( startdate ) > week_start_num, 0, 7 ))', what is the correct way to accomplish it?

Blank · Accepted Answer · 2017-09-27 09:59:13Z

1

Try this:

SET my_new_date = startdate
                  - INTERVAL 
                  ( 
                    WEEKDAY(startdate)
                    - week_start_num
                    + IF(WEEKDAY(startdate) > week_start_num, 0, 7)
                  )
                  DAY;

answered Sep 27, 2017 at 9:59

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Procedure DATE Variable Operation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related