September 14, 2006 at 8:59 am
Hi i'm trying to figure out whats the best approach to create the data for a date table that will contain weeks of the year based on that each monday will mark a new week. Also to be able to generate this date range in many years such as to 2100.
my desired output is this:
week start date end date
1 01/02/2006 01/09/2006
2 01/09/2006 01/16/2006
....
52 12/26/2006 01/02/2007
September 14, 2006 at 9:30 am
John
I've taken the liberty of making the following the Sunday the end date of the week, rather than the Monday that starts the next week, as you have in your desired output. Also, week 52 actually begins on 25th December! You'll be able to tweak this to fit your exact requirements, I'm sure. This works for week numbers up to 255 (because that is how many numbers are in the spt_values table) so for dates beyond that you'll need to think of a different way of generating numbers. There are quite a few threads on this site on that subject.
select
number as week,
dateadd (wk, number - 1, '2006-01-02 00:00:00.000') as startdate,
dateadd (dd, -1, dateadd (wk, number, '2006-01-02 00:00:00.000')) as enddate
from
master.dbo.spt_values
where
number between 1 and 52
and
name is null
John
September 17, 2006 at 10:17 pm
Do you need ISO weeks by any chance?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply