Calculating Week Start and Week End Dates Dynamically

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.



dbo.WeekStartDate(DateTimeColumn) AS WeekStartDate,

DATEADD(dd, 6, dbo.WeekStartDate(DateTimeColumn)) AS WeekEndDate,

DATEADD(dd, -1, dbo.WeekStartDate(DateTimeColumn)) AS PreviousWeekEndDate,



/****** Creates a Function to Establish Week Start Date (the nearest Sunday before)  ******/

CREATE FUNCTION [dbo].[WeekStartDate] (@MidWeekDate DateTime)

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


Thanks GPL from United Kingdom

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s