MySQL: How to calculate weeks out from a specific date?

0 votes
asked Oct 18, 2010 by andrew

I need to calculate the weeks out from a date in my MySQL select statement. There is a date column in one of the tables, and I need to calculate how many weeks away the date is.

SELECT EventDate, (calculation) AS WeeksOut FROM Events;

Example:

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

3 Answers

0 votes
answered Oct 18, 2010 by omg-ponies

Use the DATEDIFF function:

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

The problem with WEEKS is that it won't return correct results for dates that cross over January 1st.

The 0 is the number of decimal places to use in the ROUND function.

0 votes
answered Oct 18, 2010 by ike-walker

Here's a simple way to do it:

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 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)
0 votes
answered Oct 3, 2014 by kirk-backus

In order to get past the whole "New Year" issue 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 (which is Monday) and last Friday would return 1 using this method, but would return 0 with the DATEDIFF method

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...