I faced a scenario where I need to get the first and last date of given
ISO Week
To know about the concept of ISO week of the year please refer the following URL:
http://en.wikipedia.org/wiki/ISO_week_date Here the function is as follows: CREATE FUNCTION [dbo].[FncGetISOStartEndDateGivenWeekNo]
(
@GetFor VARCHAR(25),
@WeekNo INT,
@Year INT = NULL
)
RETURNS DATETIME
AS
BEGIN
DECLARE @DTSTARTENDDATE DATETIME
DECLARE @DayofYear AS INT
SELECT @DayofYear = (@WeekNo *7)-1
DECLARE @DATEDIFF AS INT
DECLARE @FLAG AS BIT
IF ((@Year = NULL) OR (@YEAR IS NULL))
BEGIN
SET @Year = YEAR(GETDATE())
END
SET @FLAG = 0
IF (@GetFor = 'TODATE')
BEGIN
SET @DATEDIFF = 0
SET @FLAG = 1
END
ELSE IF (@GetFor = 'FROMDATE')
BEGIN
SET @DATEDIFF = 6
SET @FLAG = 1
END
IF (@FLAG = 1)
BEGIN
DECLARE @date AS DATETIME
DECLARE @stDateofCurYear DATETIME
SET @date = CAST((CAST(@Year AS VARCHAR(5))+'0101') AS DATETIME)
--Region For getting the First Day and First Week of the Current year
SELECT @stDateofCurYear = OuterQry.CurrYrStart FROM (
SELECT
-- First day of first week of current year
CurrYrStart = DATEADD(DAY, (DATEDIFF(DAY, '17530101', InnerQry.Jan4th) / 7) * 7, '17530101')
FROM
(
SELECT
--Get the January 4th Day of input date's year
Jan4th =
DATEADD(dd,3,DATEADD(yy,DATEDIFF(yy,0,@date),0))
) InnerQry
) as OuterQry
--Endregion
SELECT @DTSTARTENDDATE = DATEADD(DAY, (@DayofYear-@DATEDIFF), @stDateofCurYear)
END
ELSE
BEGIN
SET @DTSTARTENDDATE = NULL
END
RETURN @DTSTARTENDDATE
END
Run for Testing
--------------------- select dbo.FncGetISOStartEndDateGivenWeekNo('FROMDATE',45,2012) -- 2012-11-05 00:00:00.000
select dbo.FncGetISOStartEndDateGivenWeekNo('TODATE',45,2012) -- 2012-11-11 00:00:00.000
select dbo.FncGetISOStartEndDateGivenWeekNo('FROMTODATE',45,2012) -- Print NULL
First I am getting the number of days with respect to given week Here 45.
select (45*7) -1 -- Get the Number of days and sub by 1. Here 314
In Inner query I am finding the 4th Day of given year. If given year is 2012 then 04-Jan-2012
To find the First day of First ISO Week, We need to pass 4th date to outer query.
The outer query works as follows:
--- No of days
select DATEDIFF(DAY, '17530101', '20120104')
-- No of weeks
select DATEDIFF(DAY, '17530101', '20120104')/7
--Get the StartDay of ISO Week 1
select DATEADD(DAY, 13514 * 7, '17530101')
For FromDate : --For FromDate Subtracting By 6
select DATEADD(DAY, (314-6), '20120102')
To get Last Date: -- ToDate Subtracting By 0
select DATEADD(DAY, (314-0), '20120102')