Getting weeks between two dates in MySql

[code lang=”sql”]
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 iDateFrom 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

0

About the author

Janaki(http://mjanaki.com) - A Software developer with 9+ years of experience in multiple technology projects across web, mobile, and cloud applications with expertise in PHP, C#, MySQL, Web services and open source platforms.

Similar Posts

Uninstall MySql on a Mac OS X
Creating new mysql user using query

Comments are closed.