CODEWITHSUNDEEP
mysql
niveditha
niveditha

Reputation:

MySQL week calculation between two dates

I've been stuck with this issue for days, which is something like calculating the numbers of weeks that lies between two days, for example:

Select @Days = (datediff( week, @pdtFromDate, @pdtToDate) - 1) * 5

This returns the output as 257.

I need to convert this script into MySQL.

Upvotes: 19

Views: 23901

Answers (3)

Arash Rahmani
Arash Rahmani

Reputation: 11

It's not correct to just divide the difference between dates to 7. We should consider the date that the week starts from. You can find the first day of each two weeks depending on starting day of week in your region (e.g., my week starts from Saturday) then compute the weeks between two dates e.g.,two Saturdays using devide by 7.

If the week starts on Sunday do this:

SELECT DATEDIFF(DATE_FORMAT(DATE_ADD(MySecondDate, INTERVAL(1-DAYOFWEEK(MySecondDate)) DAY), '%Y-%m-%d'),DATE_FORMAT(DATE_ADD(MyFirstDate, INTERVAL(1-DAYOFWEEK(MyFirstDate)) DAY), '%Y-%m-%d')) DIV 7+1;

If the week starts on Monday do this:

SELECT DATEDIFF(DATE_FORMAT(DATE_ADD(MySecondDate, INTERVAL(-WEEKDAY(MySecondDate)) DAY), '%Y-%m-%d'),DATE_FORMAT(DATE_ADD(MyFirstDate, INTERVAL(-WEEKDAY(MyFirstDate)) DAY), '%Y-%m-%d')) DIV 7+1;

For example if first date is "2022-01-01" and second date is "2022-03-01" and start day is Monday you can use this sample code:

SELECT DATEDIFF(DATE_FORMAT(DATE_ADD("2022-03-01", INTERVAL(-WEEKDAY("2022-03-01")) DAY), '%Y-%m-%d'),DATE_FORMAT(DATE_ADD("2022-01-01", INTERVAL(-WEEKD
Y("2022-01-01")) DAY), '%Y-%m-%d')) DIV 7+1;

Upvotes: 1

Saurav Dangol
Saurav Dangol

Reputation: 904

you could also try this as it separates weeks and days.

    SET @day1=DATE('2015-02-02');
    SET @day2=DATE('2015-02-10');
    SELECT CONCAT(SUBSTRING_INDEX(ABS(DATEDIFF(@day1,@day2)/7),'.',1),'Weeks ',
    SUBSTRING_INDEX(ABS(DATEDIFF(@day1,@day2)),'.',1)-SUBSTRING_INDEX(ABS(DATEDIFF(@day1,@day2))/7,'.',1)*7,'Days'
    )AS diff

Upvotes: 0

James C
James C

Reputation: 14149

DATEDIFF(@date1, @date2)/7

That returns a fraction which I'm guessing you'll want to round in some way with CEIL(), ROUND() or FLOOR()

My test example with two defined dates:

SELECT FLOOR(DATEDIFF(DATE(20090215), DATE(20090101))/7);

Upvotes: 35

Related Questions