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]
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]
Comments
Post a Comment