Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 auxiliary week date table

Author  Topic 

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-16 : 05:03:37
Hi there,

I wonder if anyone can help me with this.

I have a date table that has the following columns
Weekno (1 - 52)
PeriodNo (1 - 12)
WeekStartDate (Every Sunday)
WeekEndDate (Every Saturday)
Year (Financial year i.e. 2009)

I want to be able to populate the table but I'm not sure how to do this so that I'm populateing the WeekStartDate with Saturday dates and WeekEndDate with Sunday dates.

Does anyone have existing code or know where I can get it that may help.

Many thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-16 : 09:25:03
I'm a little bored at work so I just compiled something more or less on the fly. Don't know why my datepart(wk, ...) reports the wrong weeknumber...and I haven't checked it all too much for the quality but I guess it might point you in the right direction.
--> Setting saturday as the starting day of the week
SET DATEFIRST 6

DECLARE @table table (
WeekNo int,
MonthNo int,
WeekStartDate datetime,
WeekEndDate datetime,
FinancialYear int
)
DECLARE
@WeekStartDate datetime,
@WeekEndDate datetime

SET @WeekStartDate = '2009-01-04'
SET @WeekEndDate = '2009-01-10'

WHILE YEAR(@WeekStartDate) <= 2011
BEGIN
INSERT INTO @table
SELECT DATEPART(wk, @WeekStartDate)-1, DATEPART(mm, @WeekStartDate), @WeekStartDate, @WeekEndDate, YEAR(@WeekStartDate)

SET @WeekStartDate = DATEADD(dd, 7, @WeekStartDate)
SET @WeekEndDate = DATEADD(dd, 7, @WeekEndDate)

END

SELECT TOP 200 * FROM @table


- Lumbago
Go to Top of Page

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-19 : 05:56:38
Thanks for the help Lumbago. It's helped a lot. I ran the code and re-arranged the dates. i'll work on making the end of march week 1.

Cheers

P
Go to Top of Page
   

- Advertisement -