CODEWITHSUNDEEP
sqlt-sqldate
user1106741
user1106741

Reputation: 237

T-SQL - Get start and end date based on week number.

Our business considers a week from (Monday - Sunday). I need to write a T-SQL function, which passes in year, week no as parameters and it will return the start and end date of that week. However I've seen many examples but the problem lies within the year overlapping.

e.g December 26, 2011 (Monday) - January 01, 2012 (Sunday)... << Would want to consider this as the last week of 2011.

And also in T-SQL the datepart(ww,DATE) considers Sunday as the start of the week??

Or Am I better creating my own table with the week no and storing its start and end date?

Upvotes: 2

Views: 10526

Answers (4)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

How about this one:

--DROP FUNCTION dbo.GetBusinessWeekStart
CREATE FUNCTION dbo.GetBusinessWeekStart(
    @Year SMALLINT,
    @Week TINYINT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @FirstMonday TINYINT
    DECLARE @Result DATETIME

    IF ISNULL(@Week,0)<1 OR ISNULL(@Year,0)<1900
        BEGIN
            SET @Result= NULL;
        END
    ELSE
        BEGIN
            SET @FirstMonday=1

            WHILE DATEPART(dw,CONVERT(DATETIME, '01/0' + CONVERT(VARCHAR,@FirstMonday) + '/' + CONVERT(VARCHAR,@Year)))<>2
            BEGIN
                SET @FirstMonday=@FirstMonday+1
            END

            SET @Result=CONVERT(DATETIME, '01/0' + CONVERT(VARCHAR,@FirstMonday) + '/' + CONVERT(VARCHAR,@Year))
            SET @Result=DATEADD(d,(@Week-1)*7,@Result)

            IF DATEPART(yyyy,@Result)<>@Year
                BEGIN
                    SET @Result= NULL;
                END
        END

    RETURN @Result
END
GO

--Example
SELECT dbo.GetBusinessWeekStart(2011,15) [Start],dbo.GetBusinessWeekStart(2011,15)+6 [End]

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

DECLARE
    @Year INT,
    @Week INT,
    @FirstDayOfYear DATETIME,
    @FirstMondayOfYear DATETIME,
    @StartDate DATETIME,
    @EndDate DATETIME

SET @Year = 2011
SET @Week = 52

-- Get the first day of the provided year.
SET @FirstDayOfYear = CAST('1/1/' + CAST(@YEAR AS VARCHAR) AS DATETIME)

-- Get the first monday of the year, then add the number of weeks.
SET @FirstMondayOfYear = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, @FirstDayOfYear), @FirstDayOfYear)), 0)

SET @StartDate = DATEADD(WEEK, @Week - 1, @FirstMondayOfYear)

-- Set the end date to one week past the start date.
SET @EndDate = DATEADD(WEEK, 1, @StartDate)

SELECT @StartDate AS StartDate, DATEADD(SECOND, -1, @EndDate) AS EndDate

Upvotes: 3

Callie J
Callie J

Reputation: 31296

This is the sort of thing where you're better off creating a calendar table: the main issue is knowing how far into the past/future to populate it, but beyond that, have the table schema include week number and date. Depending on what other date-based queries you want to do, you might want to have additional column to break the date down into its constituant parts (e.g., have three separate columns for day/month/year/name of day, etc).

Upvotes: 0

leomeurer
leomeurer

Reputation: 752

You should create a table with the holidays and days you don´t wnat to consider in your counts.

After this count the days between the initial date and final date. (Step1)

Select in your table to check how many days are between your ini and final dates. (Step2)

Finally subtract the result of step 2 with result of step 1;

Upvotes: 0

Related Questions