How To Solve Now

Getting weeks between two dates in MySql

---

---

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_getDateFormat` $$
CREATE PROCEDURE `sp_getDateFormat`(StartDate varchar(15),  EndDate varchar(15),  Cond varchar(5))
    READS SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE totalDay INT DEFAULT 0;
  DECLARE i int default 0;
  DECLARE newDate varchar(15);
  DECLARE toDate varchar(15);
  DECLARE totalWeek int default 0;
  DECLARE totalMonth int default 0;
  DECLARE totalYear int default 0;
  DECLARE j int default 0;


  IF Cond='d' THEN
    Select DATEDIFF(STR_TO_DATE(EndDate,'%m/%d/%Y'),STR_TO_DATE(StartDate,'%m/%d/%Y')) into totalDay;

    -- Creare temprary table
    DROP TEMPORARY TABLE IF EXISTS table_temp;
    CREATE TEMPORARY TABLE table_temp(
     `DateFrom` VARCHAR(15) NOT NULL,
     `DateTo` VARCHAR(15) NOT NULL
    ) ENGINE=Memory;
    -- Fisnsh------------------------------------------------------------

    WHILE i  <= totalDay DO
        Select DATE_ADD(STR_TO_DATE(StartDate,'%m/%d/%Y'),INTERVAL i day) into newDate;
        INSERT INTO table_temp VALUES (newDate,newDate);
        SET  i = i + 1;
    END WHILE;

  ELSEIF Cond='w' THEN
    Select DATEDIFF(STR_TO_DATE(EndDate,'%m/%d/%Y'),STR_TO_DATE(StartDate,'%m/%d/%Y')) into totalDay;
    Set totalWeek=CEILING(totalDay/7);
    Select STR_TO_DATE(StartDate,'%m/%d/%Y') into newDate;
    Select STR_TO_DATE(StartDate,'%m/%d/%Y') into toDate;

     -- Creare temprary table
    DROP TEMPORARY TABLE IF EXISTS table_temp;
    CREATE TEMPORARY TABLE table_temp(
     `DateFrom` VARCHAR(15) NOT NULL,
     `DateTo` VARCHAR(15) NOT NULL
    ) ENGINE=Memory;
    -- Fisnsh------------------------------------------------------------

   while i<totalWeek DO
      SELECT DATE_ADD(newDate,INTERVAL (1-DAYOFWEEK(newDate)) day) into newDate;
      SELECT DATE_ADD(toDate,INTERVAL (7-DAYOFWEEK(toDate)) day) into toDate;
      INSERT INTO adtinternal.table_temp VALUES (newDate,toDate);

      Select DATE_ADD(newDate,INTERVAL 7 day) into newDate;
      Select DATE_ADD(toDate,INTERVAL 7 day) into toDate;

      SET i=i+1;
   end while;

 ELSEIF Cond='m' THEN
    Select MONTH(STR_TO_DATE(EndDate,'%m/%d/%Y'))-MONTH(STR_TO_DATE(StartDate,'%m/%d/%Y')) into totalMonth;

    Select STR_TO_DATE(StartDate,'%m/%d/%Y') into newDate;
    Select STR_TO_DATE(EndDate,'%m/%d/%Y') into toDate;

     -- Creare temprary table
    DROP TEMPORARY TABLE IF EXISTS table_temp;
    CREATE TEMPORARY TABLE table_temp(
     `DateFrom` VARCHAR(15) NOT NULL,
     `DateTo` VARCHAR(15) NOT NULL
    ) ENGINE=Memory;
    -- Fisnsh------------------------------------------------------------

   while i<=totalMonth DO
      SELECT DATE_ADD(newDate,INTERVAL (1-DAYOFMONTH(newDate)) day) into newDate;
      SELECT DATE_ADD(newDate,INTERVAL (getDaysInMonth(MONTH(newDate),YEAR(newDate))-DAYOFMONTH(newDate)) day) into toDate;

      INSERT INTO adtinternal.table_temp VALUES (newDate,toDate);

      Select DATE_ADD(newDate,INTERVAL getDaysInMonth(MONTH(newDate),YEAR(newDate)) day) into newDate;
      -- Select DATE_ADD(toDate,INTERVAL getDaysInMonth(MONTH(newDate),YEAR(newDate)) day) into toDate;

      SET i=i+1;
    end while;


 ELSEIF Cond='y' THEN
    CREATE TEMPORARY TABLE table_temp(
     `DateFrom` VARCHAR(15) NOT NULL,
     `DateTo` VARCHAR(15) NOT NULL
    ) ENGINE=Memory;

  INSERT INTO adtinternal.table_temp VALUES (STR_TO_DATE(StartDate,'%m/%d/%Y'),STR_TO_DATE(EndDate,'%m/%d/%Y'));
END IF;
-- -------------------------------------------------------
  SELECT * from table_temp;

END $$

DELIMITER ;


-- and finally call the procedure as 
call sp_getDateFormat('01/01/2014','01/31/2014','w')