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.


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

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s