473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

Feb 16 '06 #1
5 4244
rj********@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

Feb 16 '06 #2
.... and WEEK might be even more useful, I should have read a bit
further.
--
Eric Lafontaine

Feb 16 '06 #3
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?

Feb 16 '06 #4
rj********@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/...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

Feb 16 '06 #5
>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 <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.)
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
Feb 16 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: John | last post by:
If anyone can help me out with a good way to do this in javascript I would greatly appreciate it. I need to compute three dates in javascript - all of which relate to a given date, say todays...
9
by: rjfjohnson | last post by:
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...
8
by: sherifffruitfly | last post by:
Hi, I've been searching as best I can for this - coming up with little. I have a file that is full of lines fitting this pattern: (?<year>\d{4}),(?<amount>\d{6,7}) I'm likely to get a...
1
by: krishnadrk | last post by:
ReceiptDesc> <Receipt> po_nbr>123</po_nbr> ReceiptDtl> <receipt_date>
4
jiffylube
by: jiffylube | last post by:
I'm putting together a retail comp report that compares the current day stats to the same day, previous year. Is there a code I can use to get last year, same day? For example: This year stats=...
0
by: pratapmysql | last post by:
Hello All, I am trying to get same day (name) of week of last year in this case date of day is not constraint eg. if december 2007-12-19 day is wednesday ,then last year 2006 wednesday for same is...
1
SHOverine
by: SHOverine | last post by:
Recently my web host decided to "upgrade". This change rendered many of my pages useless and I am scrambling to fix the issues, so you may see several posts from me in the coming days. My first...
5
by: Brett | last post by:
Hello, Is it possible to have just one criteria and have it apply to a group of queries? I am trying to create a report with the separate results of 4 queries based on a prompt for the user...
7
by: kooroshkdt | last post by:
hi everyone, i'm not beginner but i learn programing in hack kind, i don;t have any idea about my problem(in C++ wrote)!!! Read Plz post if u have any Idea, i want to here it, it will be...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.