Showing posts with label How to Get Start date and EndDate of Week in Sql Server. Show all posts
Showing posts with label How to Get Start date and EndDate of Week in Sql Server. Show all posts

2014/10/16

How to Get Start date and EndDate of Week in Sql Server

DECLARE @REPORT_DATE DATETIME
SELECT @REPORT_DATE = '2014-10-16'
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT  @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW,  @REPORT_DATE) - 1)

--GET THE WEEK END DATE
SELECT  @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW,  @REPORT_DATE))

PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)
----------------------------------------------------------------------------------------------------------

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

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

 What is AI? Artificial Intelligence (AI) is the ability of machines or computer systems to perform tasks that normally require human intell...