# Same day last year problem

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
Hey,

Today is Thursday 16-Feb-06.  The same thursday last year is 17-Feb-05.
Because I am comparing daily sales between years, I need to know the
date of the same weekdayname as last year, so that I am comparing
Saturdays with Saturdays, Sundays with Sundays, etc

ie, 16-feb-06 goes to 17-feb-05
28-july-06 goes to 27-july-05 and so on

I have tried all sorts of dateadd('2004-01-01'............. etc, and I
am all out of ideas.

Any suggestions would be very much appreciated.

Thanks
Ryan

## Re: Same day last year problem

rjfjohnson@hotmail.com wrote:

What exactly do you mean by 'the same thursday'?

Is it the third thursday in february? If so, how do you handle the
fifth xxx of february in a leap year?

Is it the thursday of week number n? If so, when does week 1 start?
First day of january? Then weeks start on different days in different
years.
First saturday (or sunday or wednesday...) of january? Then is it week
0 before that? Or week 53 of the previous year?

DAYOFYEAR and DAYNAME('2006-01-01') might be helpful.
--
Eric Lafontaine

## Re: Same day last year problem

... and WEEK might be even more useful, I should have read a bit
further.
--
Eric Lafontaine

## Re: Same day last year problem

Eric,

I am onto something with subtracting 52 weeks from date1.  This works,
until I encounter a February 29th...

Any clues from here?

## Re: Same day last year problem

rjfjohnson@hotmail.com wrote:

I don't think so. 52 weeks are 364 days, so you will have cases where
your "same date" falls in a different month. Leap years should not add
any disturbance.
Same old question: has your "same date" to be in the "same" month?

Combination of DAYOFWEEK and WEEK, see
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
At least, you will get something standard and consistent.

And just thinking of it: are your sales depending in any way on such
things as Easter or Carnival? Any comparison is then due to be
flawed...
--
Eric Lafontaine

## Re: Same day last year problem

Decide what you want.  Given that one year ago from your date isn't
the right weekday, what do you want:
(a) The first <Weekday> *BEFORE* 1 year ago
(b) The first <Weekday> *AFTER* 1 year ago
(c) (a) or (b), whichever is closest.
And what happens if the result isn't in the previous year?  (e.g.
the comparison date for Jan 2, XXXX might be Dec 31, XXXX-2 , or
the comparison date for Dec 31, XXXX might be Jan 2, XXXX.)

Seems like maybe you want (c), but that's unclear.  You might end
up with one date being used as a comparison more than once, and
another not at all.

You can often do manipulation with days of the week by taking the
weekday number of the date you've got, subtract it from the weekday
number of the date you want, possibly taking that value mod 7, and