By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Calendar: x weeks ago always starting on a Sunday

P: 1
The result of the query should be all dates from today - 5 weeks ago, where the first day is a Sunday using a calendar table specifically for this business:

The current week is defined as w/e 15th Jan and it's the 5 weeks back from then so start w/e 11th Dec

e.g.

select distinct day_date, day_name, week_number, weekstart_date
from
calendar
where
day_date between (curdate() - INTERVAL 35 day) and curdate()

returns data from

09/12/2011 - today, which is fine, but the first date I am interested in is the Sunday:

day_date day_name week_number weekstart_date weekend_date
2011-12-09 00:00:00 Friday 50 2011-12-05 00:00:00 2011-12-11 00:00:00
2011-12-10 00:00:00 Saturday 50 2011-12-05 00:00:00 2011-12-11 00:00:00
2011-12-11 00:00:00 Sunday 51 2011-12-05 00:00:00 2011-12-11 00:00:00

.....


2012-01-12 00:00:00 Thursday 2 2012-01-09 00:00:00 2012-01-15 00:00:00
2012-01-13 00:00:00 Friday 2 2012-01-09 00:00:00 2012-01-15 00:00:00


What SQL do I write to ensure that the first Sunday will always be selected as the first day of the date range?
Jan 13 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
Well you need to account for the current date's weekday.

You need to take 35, add 7, (or just take 42) and then subtract the weekday() of the current date. With the exception of sunday of course.
Jan 13 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.