Adding Week Start and Week End dates to your Date Dimension table can make navigating your dimensional model extremely handy and tasty to the end user.
From an existing Date Dimension table, you can add Week Start and Week End columns to your table and then make use of functions to assist in the task of populating the new values. After you add the new date columns for Week Start, Week End and optionally, Previous Week End (can be very useful), and have created the Week Start function below, issue a command similar to the following to test and inspect the results (remember to substitute DateTimeColumn with your own DateTime value from your own Date Dimension table). **The following example uses a Sunday to Saturday week.
SELECT DateTimeColumn, dbo.WeekStartDate(DateTimeColumn) AS WeekStartDate, DATEADD(dd, 6, dbo.WeekStartDate(DateTimeColumn)) AS WeekEndDate, DATEADD(dd, -1, dbo.WeekStartDate(DateTimeColumn)) AS PreviousWeekEndDate, FROM DateDimensionTable /****** Creates a Function to Establish Week Start Date (the nearest Sunday before) ******/ CREATE FUNCTION [dbo].[WeekStartDate] (@MidWeekDate DateTime) RETURNS DateTime AS BEGIN DECLARE @WeekCommence DateTime SET @MidWeekDate = DATEADD(dd, DATEDIFF(d, 0, @MidWeekDate),0) SET @WeekCommence = DateAdd(d, -((@@DATEFIRST + DatePart(dw, @MidWeekDate) -1) % 7), @MidWeekDate) RETURN @WeekCommence END GO
Thanks GPL from United Kingdom http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307