create function PremierJourApres(@UneDate datetime, @QuelJour integer) returns datetime
as
begin
return DateAdd(day, 6-((DatePart(dw, @UneDate)+6-@QuelJour) % 7), @UneDate)
end
create function NumeroSemaine(@UneDate datetime) returns integer
as
begin
declare @PremierJeudi datetime, @DebutSemaine1 datetime
declare @TheDate datetime, @StartOfYear datetime
set @TheDate = convert(datetime, convert(varchar, @UneDate, 103), 103)
set @StartOfYear = DateAdd(day, 1-DatePart(dy, @TheDate), @TheDate)
set @PremierJeudi = dbo.PremierJourApres(@StartOfYear, 4)
set @DebutSemaine1 = DateAdd(day, -4, @PremierJeudi)
return 1+(DateDiff(day, @DebutSemaine1, @UneDate) / 7)
end