MySQL: how to calculate weeks from a specific date?

Question

I need to calculate weeks from a date in my MySQL select statement. One of the tables has a date column, and I need to calculate how many weeks are left.

SELECT EventDate, (calculation) AS WeeksOut FROM Events; 

Example:

• 6 days, weeks = 0
• 7 days, weeks = 1
• 13 days, weeks = 1
• 14 days, weeks = 2

Answer 1

Use the DATEDIFF function :

 ROUND(DATEDIFF(end_date, start_date)/7, 0) AS weeksout 

The problem with WEEKS is that it will not return the correct results for dates that cross January 1.

0 - the number of decimal places used in ROUND .

Answer 2

Here is an easy way to do this:

 SELECT EventDate, (week(EventDate) - week(curdate())) AS WeeksOut FROM Events; 

Example:

 mysql> select week('2010-11-18') - week ('2010-10-18'); +------------------------------------------+ | week('2010-11-18') - week ('2010-10-18') | +------------------------------------------+ | 4 | +------------------------------------------+ 1 row in set (0.00 sec) 

Another option is to calculate the interval in days and divide by 7:

 SELECT EventDate, datediff(EventDate,curdate())/7 AS WeeksOut FROM Events; 

Example:

 mysql> select datediff('2010-11-18' , '2010-10-18') / 7; +-------------------------------------------+ | datediff('2010-11-18' , '2010-10-18') / 7 | +-------------------------------------------+ | 4.4286 | +-------------------------------------------+ 1 row in set (0.00 sec) 

Answer 3

To go through the whole New Year, and you still want to use WEEK() , I found the following method quite effective.

 SELECT YEAR(end_date)*52+WEEK(end_date) - YEAR(start_date)*52 - WEEK(start_date) as weeks_out FROM events; 

The difference with this method (as opposed to the DATEDIFF method) is that it is aligned with the week. So today (this is Monday) and last Friday will return 1 using this method, but will return 0 using the DATEDIFF method