CODEWITHSUNDEEP
sql-serversql-server-2008
Des Hutch
Des Hutch

Reputation: 293

Efficient way to find week ending date without a calendar table

I am using the below SQL code in MS SSMS to find the week ending date. I often have to switch between Wednesday and Sunday as the week ending day. Side note, my data sets are often in the 100,000's.

Is there a more efficient way to do this than what I have below other than using a calendar table?

/* Declare Variables */
DECLARE @WeekendingDay VARCHAR(10);
DECLARE @DayNumber INT;
DECLARE @InputDate VARCHAR(10);
DECLARE @conInputDate DATETIME;
DECLARE @outWeekending DATETIME;
DECLARE @CovertToInt VARCHAR(10);

/* --------------------Inputs-------------------- */

/* uncomment the weekending day you want */
--Set @WeekendingDay = 'Monday'
--Set @WeekendingDay = 'Tuesday'
--Set @WeekendingDay = 'Wednesday'
--Set @WeekendingDay = 'Thursday'
--Set @WeekendingDay = 'Friday'
--Set @WeekendingDay = 'Saturday'
SET @WeekendingDay = 'Sunday';

/* Date you want the weekending of */               
SET @InputDate = '29/12/2016';

/* --------------------Process-------------------- */
SET @DayNumber = CASE @WeekendingDay
                   WHEN 'Sunday' THEN 1
                   WHEN 'Monday' THEN 2
                   WHEN 'Tuesday' THEN 3
                   WHEN 'Wednesday' THEN 4
                   WHEN 'Thursday' THEN 5
                   WHEN 'Friday' THEN 6
                   WHEN 'Saturday' THEN 7
                 END;


SET @conInputDate = CONVERT(DATETIME, @InputDate, 103);
SET @outWeekending = DATEADD(dd,
                             CASE WHEN DATEPART(DW, @conInputDate) = @DayNumber
                                  THEN 0
                                  ELSE -1 * DATEPART(DW, @conInputDate) + 7
                                       + @DayNumber
                             END, @conInputDate);

/* --------------------Output-------------------- */
PRINT @outWeekending;

Upvotes: 3

Views: 360

Answers (1)

Andrew O'Brien
Andrew O'Brien

Reputation: 1823

This will give you the Monday of the week. Depending on what day you want to use as the end you can adjust it as needed.

DECLARE @date DATETIME = GETUTCDATE()-5

SELECT
    @date
    ,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0)    --Monday
    ,DATEADD(DD,1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Tuesday
    ,DATEADD(DD,2,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Wednesday
    ,DATEADD(DD,3,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Thursday
    ,DATEADD(DD,4,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Friday
    ,DATEADD(DD,5,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Saturday
    ,DATEADD(DD,6,DATEADD(WEEK, DATEDIFF(WEEK, 0, @date),0))    --Sunday

Upvotes: 1

Related Questions