CODEWITHSUNDEEP
mysqlstored-proceduresplsql
Turbo Sullivan
Turbo Sullivan

Reputation: 857

MySQL Procedure DATE Variable Operation

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?

Upvotes: 0

Views: 1191

Answers (1)

Blank
Blank

Reputation: 12378

Try this:

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

Upvotes: 1

Related Questions