Same day last year problem

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

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?

Depending on your defiinition of 'same', the answers will be different.
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,

Thanks for your reply.

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 am onto something with subtracting 52 weeks from date1. This works,
> until I encounter a February 29th...

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?

> Any clues from here?

Combination of DAYOFWEEK and WEEK, see
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.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

>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

Decide what you want. Given that one year ago from your date isn't
the right weekday, what do you want:
(a) The first *BEFORE* 1 year ago
(b) The first *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.)

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

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.

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

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
adding or subtracting that number of days with dateadd().

Gordon L. Burditt