SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--Cette fonction permet de renvoyer une date en lui passant en paramètre une année, une semaine et
--le jour de la semaine que l'on veut localiser dans la semaine.
--Attention, la semaine commence le dimanche, jour 5 = jeudi
--Attention 2: on appel une autre fonction pour tester si l'année précédente compte 53 semaines afin de
--respecter la numérotation ISO des semaines (SQL server sans ça le fait à l'américaine).

CREATE FUNCTION [dbo].[SEMAINE_DEPUIS_DATE]
(@DATE DATETIME)
RETURNS VARCHAR(8) AS

BEGIN

DECLARE @Result AS VARCHAR(8);
DECLARE @NUMSEM AS VARCHAR(8);

SET @NUMSEM = DATEPART(WEEK, @DATE)

SET @Result = (CASE @NUMSEM WHEN 53 THEN 1 ELSE @NUMSEM END )

RETURN @Result


END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[SEMAINE_ISO_DEPUIS_DATE]
(
@DATE DATETIME
)
returns int
AS
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear int

SELECT
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
CASE
WHEN NextYrStart <= @DATE
THEN NextYrStart
WHEN CurrYrStart <= @DATE
THEN CurrYrStart
ELSE PriorYrStart
END,@DATE)/7)+1
FROM
(
SELECT
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
FROM
(
SELECT
--Find Jan 4 for the year of the input date
Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@DATE),0))
) aa
) a

return @WeekOfYear

END
GO