Search code examples
sqlmysqldate-manipulation

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

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
Solution

  • 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.