Write a sql server function to find the Start and End date of given ISO week

Nagasundar_Tn
Posted by Nagasundar_Tn under Sql Server category on | Points: 40 | Views : 2622
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')  

Comments or Responses

Login to post response