auxiliary week date table - SQL Server Forums

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources