0

I am using the week function like so:

SELECT 
    sub.id AS ARID,
    WEEK(my.data) AS Week,

Is it possible to add the week beginning date that the week corresponds to?

Something like:

SELECT 
    sub.id AS ARID,
    WEEK(my.data) AS Week,
    MIN(my.data) AS WKBeginning

Using MIN in this way I remove a lot of data unintentionally - it should be divorced fromt he data even. Put another way, I'd like to display Week as a date rather than a number that is along the lines 'Monday-2014-09-04'

Is that possible?

Doug Fir
  • 19,971
  • 47
  • 169
  • 299
  • 1
    My answer was incorrect under some circumstances, I've updated it with a better (and correct) solution. – jpw Oct 27 '14 at 20:07

1 Answers1

1

My first answer turned out to be incorrect if the source table didn't include the full range of dates for the week (specifically the first day of the week).

The correct way is to use ADDDATE():

ADDDATE(my.data, INTERVAL 1-DAYOFWEEK(my.data) DAY) AS CorrectWKBeginning,

A correlated sub-query should does not work when the first day of the week is not included in the data:

SELECT 
  id,
  WEEK(d) AS Week,
  ADDDATE(d, INTERVAL 1-DAYOFWEEK(d) DAY) AS CorrectWKBeginning,
  (SELECT MIN(d) FROM t WHERE WEEK(d) = WEEK(t2.d)) as IncorrectWKBeginning
FROM t t2

Sample SQL Fiddle

You might have to use week() with parameters to set the start day of the week and if it counts from 0-53 or 1-53, see the MySQL manual for reference.

jpw
  • 44,361
  • 6
  • 66
  • 86